WIP

main/plugins/org.talend.designer.components.localprovider/components/tBigQueryRow/BigQueryRowQueryHelper.javajet

@@ -0,0 +1,170 @@
+<%@ jet
+	    imports="
+		java.util.List
+	    "
+%>
+
+class BigQueryUtil_<%=cid%> {
+	String projectId;
+	com.google.api.services.bigquery.Bigquery bigqueryclient = null;
+	String tokenFile;
+	boolean useLargeResult = false;
+	String tempDataset;
+	String tempTable;
+	
+	public BigQueryUtil_<%=cid%>(String projectId, com.google.api.services.bigquery.Bigquery bigqueryclient, String tokenFile) {
+		this.projectId = projectId;
+		this.bigqueryclient = bigqueryclient;
+		this.tokenFile = tokenFile;
+	}
+	
+	private String genTempName(String prefix){
+		return "temp_" + prefix + java.util.UUID.randomUUID().toString().replaceAll("-", "") + "<%=cid%>".toLowerCase().replaceAll("[^a-z0-9]", "0").replaceAll("^[^a-z]", "a") + Integer.toHexString(java.util.concurrent.ThreadLocalRandom.current().nextInt());
+	}
+	
+	public void cleanup() throws Exception{
+		if(useLargeResult){
+			bigqueryclient.tables().delete(projectId, tempDataset, tempTable).execute();
+			bigqueryclient.datasets().delete(projectId, tempDataset).execute();
+		}
+	}
+	
+	private String getLocation(com.google.api.services.bigquery.model.JobConfigurationQuery queryConfig) throws Exception {
+		String location = null;
+		com.google.api.services.bigquery.model.JobConfiguration config = new com.google.api.services.bigquery.model.JobConfiguration();
+		config.setQuery(queryConfig);
+		config.setDryRun(true);
+		com.google.api.services.bigquery.model.Job job = new com.google.api.services.bigquery.model.Job();
+		job.setConfiguration(config);
+		List<com.google.api.services.bigquery.model.TableReference> referencedTables = bigqueryclient.jobs().insert(projectId, job).execute().getStatistics().getQuery().getReferencedTables();
+		if(referencedTables != null && !referencedTables.isEmpty()) {
+			location = bigqueryclient.tables().get(projectId, referencedTables.get(0).getDatasetId(), referencedTables.get(0).getTableId()).execute().getLocation();
+		}
+		return location;
+	}
+	
+	private void createDataset(String location) throws Exception {
+		com.google.api.services.bigquery.model.Dataset dataset = new com.google.api.services.bigquery.model.Dataset().setDatasetReference(new com.google.api.services.bigquery.model.DatasetReference().setProjectId(projectId).setDatasetId(tempDataset));
+		if(location != null) {
+			dataset.setLocation(location);
+		}
+		String description = "Dataset for BigQuery query job temporary table";
+		dataset.setFriendlyName(description);
+    	dataset.setDescription(description);
+    	bigqueryclient.datasets().insert(projectId, dataset).execute();
+	}
+	
+	public com.google.api.services.bigquery.model.Job executeQuery(String query, boolean useLargeResult) throws Exception{
+		com.google.api.services.bigquery.model.JobConfigurationQuery queryConfig = new com.google.api.services.bigquery.model.JobConfigurationQuery();
+		queryConfig.setQuery(query);
+		if(useLargeResult){
+			this.useLargeResult = true;
+			tempDataset = genTempName("dataset");
+			tempTable = genTempName("table");
+			createDataset(getLocation(queryConfig));
+			queryConfig.setAllowLargeResults(true);
+			queryConfig.setDestinationTable(new com.google.api.services.bigquery.model.TableReference()
+											.setProjectId(projectId)
+											.setDatasetId(tempDataset)
+											.setTableId(tempTable));
+		}
+		
+		com.google.api.services.bigquery.model.JobConfiguration config = new com.google.api.services.bigquery.model.JobConfiguration();
+		config.setQuery(queryConfig);
+		
+		com.google.api.services.bigquery.model.Job job = new com.google.api.services.bigquery.model.Job();
+		job.setConfiguration(config);
+		
+		com.google.api.services.bigquery.model.Job insert = null;
+		com.google.api.services.bigquery.model.JobReference jobId = null;
+		try {
+			insert = bigqueryclient.jobs().insert(projectId, job).execute();
+			jobId = insert.getJobReference();
+		} catch (com.google.api.client.googleapis.json.GoogleJsonResponseException e) {
+			if(tokenFile != null){
+				try {
+					java.io.File f = new java.io.File(tokenFile);
+					boolean isRemoved = f.delete();
+					if(isRemoved){
+						<%
+				    	if(isLog4jEnabled){
+						%>
+							log.error("<%=cid%> - Unable to connect. This might come from the token expiration. Execute again the job with an empty authorization code.");
+						<%
+						}else{
+				    	%>
+							System.err.println("---> Unable to connect. This might come from the token expiration. Execute again the job with an empty authorization code.");
+						<%
+						}
+						%>
+					}else{
+						throw new java.lang.Exception();
+					}
+				} catch (java.lang.Exception ee) {
+					<%
+			    	if(isLog4jEnabled){
+					%>
+						log.error("<%=cid%> - Unable to connect. This might come from the token expiration. Remove the file " + tokenFile + " Execute again the job with an empty authorization code.");
+					<%
+					}else{
+			    	%>
+						System.err.println("---> Unable to connect. This might come from the token expiration. Remove the file " + tokenFile + " Execute again the job with an empty authorization code.");
+					<%
+					}
+					%>
+				}
+			}
+			throw e;
+		}
+		
+		<%
+		if(isLog4jEnabled){
+		%>
+			log.info("<%=cid%> - Wait for query execution");
+		<%
+		}
+		%>
+		// wait for query execution
+		while (true) {
+			com.google.api.services.bigquery.model.Job pollJob = bigqueryclient.jobs().get(projectId, jobId.getJobId()).execute();
+			com.google.api.services.bigquery.model.JobStatus status = pollJob.getStatus();
+			if (status.getState().equals("DONE")) {
+				com.google.api.services.bigquery.model.ErrorProto errorProto = status.getErrorResult();
+				if(errorProto != null){// job failed, handle it
+					<%if("AUTO".equals(resultSizeType)){%>
+						if(!useLargeResult && "responseTooLarge".equals(errorProto.getReason())){// try with large result flag
+							<%
+							if(isLog4jEnabled){
+							%>
+								log.info("<%=cid%> - Try with allow large results flag");
+							<%
+							}
+							%>
+							return executeQuery(query, true);
+						}
+					<%}%>
+					// Do not throw exception to avoid behavior changed(because it may throw "duplicate" exception which do not throw before);
+        			<%
+			    	if(isLog4jEnabled){
+					%>
+						log.error("<%=cid%> - Reason: " + errorProto.getReason() + "\nMessage: " + errorProto.getMessage());
+					<%
+					}else{
+			    	%>
+						System.err.println("---> Reason: " + errorProto.getReason() + "\nMessage: " + errorProto.getMessage());
+					<%
+					}
+					%>
+					}// else job successful
+					break;
+				}
+				// Pause execution for one second before polling job status again, to 
+				// reduce unnecessary calls to the BigQUery API and lower overall
+				// application bandwidth.
+				Thread.sleep(1000);
+			}
+		
+			return insert;
+		}
+ 		
+	}

main/plugins/org.talend.designer.components.localprovider/components/tBigQueryRow/tBigQueryRow_begin.javajet

@@ -0,0 +1,389 @@
+<%@ jet
+	    imports="
+		org.talend.core.model.process.INode 	
+		org.talend.core.model.process.ElementParameterParser 
+		org.talend.designer.codegen.config.CodeGeneratorArgument
+		org.talend.core.model.metadata.IMetadataTable 
+		org.talend.core.model.metadata.IMetadataColumn 
+		org.talend.core.model.process.IConnection
+		org.talend.core.model.process.IConnectionCategory
+		org.talend.core.model.metadata.types.JavaTypesManager
+		org.talend.core.model.metadata.types.JavaType
+		java.util.List
+	    "
+%>
+	<%@ include file="@{org.talend.designer.components.localprovider}/components/templates/Log4j/Log4jFileUtil.javajet"%>
+	<%
+	CodeGeneratorArgument codeGenArgument = (CodeGeneratorArgument) argument;
+
+	INode node = (INode)codeGenArgument.getArgument();
+
+	String cid = node.getUniqueName();
+
+	String authMode = ElementParameterParser.getValue(node,"__AUTH_MODE__");
+	String credentialsFile = ElementParameterParser.getValue(node, "__SERVICE_ACCOUNT_CREDENTIALS_FILE__");
+	String clientId = ElementParameterParser.getValue(node,"__CLIENT_ID__");
+	String clientSecret = ElementParameterParser.getValue(node,"__CLIENT_SECRET__");
+	String projectId = ElementParameterParser.getValue(node,"__PROJECT_ID__");
+	String authorizationCode = ElementParameterParser.getValue(node,"__AUTHORIZATION_CODE__");
+	String query = ElementParameterParser.getValue(node,"__QUERY__");
+	query = query.replaceAll("\n"," ");
+	query = query.replaceAll("\r"," ");
+	
+    String tokenFile = ElementParameterParser.getValue(node,"__TOKEN_NAME__");
+    boolean isLog4jEnabled = ("true").equals(ElementParameterParser.getValue(node.getProcess(), "__LOG4J_ACTIVATE__"));
+    
+    
+    // OAUTH Authentication
+    if (authMode.equals("OAUTH")) {
+		%>
+		final String CLIENT_ID_<%=cid %> = <%=clientId %>;
+		<%
+		String passwordFieldName = "__CLIENT_SECRET__";
+		%>
+	
+		<%@ include file="@{org.talend.designer.components.localprovider}/components/templates/password.javajet"%>
+		
+		final String CLIENT_SECRET_<%=cid%> = "{\"web\": {\"client_id\": \""+<%=clientId%>+"\",\"client_secret\": \"" +decryptedPassword_<%=cid%>+ "\",\"auth_uri\": \"https://accounts.google.com/o/oauth2/auth\",\"token_uri\": \"https://accounts.google.com/o/oauth2/token\"}}";
+		final String PROJECT_ID_<%=cid %> = <%=projectId %>;
+	
+		// Static variables for API scope, callback URI, and HTTP/JSON functions
+		final List<String> SCOPES_<%=cid%> = java.util.Arrays.asList("https://www.googleapis.com/auth/bigquery");
+		final String REDIRECT_URI_<%=cid%> = "urn:ietf:wg:oauth:2.0:oob";
+		final com.google.api.client.http.HttpTransport TRANSPORT_<%=cid %> = new com.google.api.client.http.javanet.NetHttpTransport();
+		final com.google.api.client.json.JsonFactory JSON_FACTORY_<%=cid %> = new com.google.api.client.json.jackson2.JacksonFactory();
+	
+		com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets clientSecrets_<%=cid%> = com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets.load(
+						new com.google.api.client.json.jackson2.JacksonFactory(), new java.io.InputStreamReader(new java.io.ByteArrayInputStream(
+								CLIENT_SECRET_<%=cid%>.getBytes())));
+	
+		com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow flow_<%=cid%> = null;
+		com.google.api.services.bigquery.Bigquery bigqueryclient_<%=cid%> = null;
+		long nb_line_<%=cid%> = 0;
+		<%
+		if(isLog4jEnabled){
+		%>
+			log.info("<%=cid%> - Service Account Scopes [https://www.googleapis.com/auth/bigquery]");
+			log.info("<%=cid%> - Redirect uris [urn:ietf:wg:oauth:2.0:oob]");
+			log.info("<%=cid%> - Attempt to load existing refresh token.");
+		<%
+		}
+		%>
+	
+		// Attempt to load existing refresh token
+	    String tokenFile_<%=cid %> = <%=tokenFile%>;
+		java.util.Properties properties_<%=cid%> = new java.util.Properties();
+		try {
+			java.io.FileInputStream inputStream_<%=cid%> = new java.io.FileInputStream(tokenFile_<%=cid %>);
+			properties_<%=cid%>.load(inputStream_<%=cid%>);
+			inputStream_<%=cid%>.close();
+		} catch (java.io.FileNotFoundException e_<%=cid%>) {
+			<%
+	    	if(isLog4jEnabled){
+			%>
+				log.warn("<%=cid%> - "+e_<%=cid%>.getMessage());
+			<%
+			}
+	    	%>
+		} catch (java.io.IOException ee_<%=cid%>) {
+			<%
+	    	if(isLog4jEnabled){
+			%>
+				log.warn("<%=cid%> - "+ee_<%=cid%>.getMessage());
+			<%
+			}
+	    	%>
+		}
+		String storedRefreshToken_<%=cid%> = (String) properties_<%=cid%>.get("refreshtoken");
+	
+		// Check to see if the an existing refresh token was loaded.
+		// If so, create a credential and call refreshToken() to get a new
+		// access token.
+		if (storedRefreshToken_<%=cid%> != null) {
+			// Request a new Access token using the refresh token.
+			com.google.api.client.googleapis.auth.oauth2.GoogleCredential credential_<%=cid%> = new com.google.api.client.googleapis.auth.oauth2. GoogleCredential.Builder().setTransport(TRANSPORT_<%=cid%>)
+					.setJsonFactory(JSON_FACTORY_<%=cid%>).setClientSecrets(clientSecrets_<%=cid%>)
+					.build().setFromTokenResponse(new com.google.api.client.auth.oauth2.TokenResponse().setRefreshToken(storedRefreshToken_<%=cid%>));
+	
+			credential_<%=cid%>.refreshToken();
+			<%
+			if(isLog4jEnabled){
+			%>
+				log.info("<%=cid%> - An existing refresh token was loaded.");
+			<%
+			}
+			%>
+			bigqueryclient_<%=cid%> = new com.google.api.services.bigquery.Bigquery.Builder(new com.google.api.client.http.javanet.NetHttpTransport(),new com.google.api.client.json.jackson2.JacksonFactory(),credential_<%=cid%>).setApplicationName("Talend").build();
+		} else {
+			<%
+			if(isLog4jEnabled){
+			%>
+				log.info("<%=cid%> - The refresh token does not exist.");
+			<%
+			}
+			%>
+			String authorizationCode_<%=cid%> = <%=authorizationCode%>;
+			if(authorizationCode_<%=cid%> == null || "".equals(authorizationCode_<%=cid%>) || "\"\"".equals(authorizationCode_<%=cid%>)) {
+				String authorizeUrl_<%=cid%> = new com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeRequestUrl(
+						clientSecrets_<%=cid%>, REDIRECT_URI_<%=cid%>, SCOPES_<%=cid%>).setState("").build();
+	
+				System.out
+						.println("Paste this URL into a web browser to authorize BigQuery Access:\n"
+								+ authorizeUrl_<%=cid%>);
+				<%
+		    	if(isLog4jEnabled){
+				%>
+					log.warn("<%=cid%> - Paste this URL into a web browser to authorize BigQuery Access:\n"
+									+ authorizeUrl_<%=cid%>);
+				<%
+				}
+		    	%>
+				throw new java.lang.Exception("Authorization Code error");
+			} else {
+				<%
+				if(isLog4jEnabled){
+				%>
+					log.info("<%=cid%> - Exchange the auth code for an access token and refesh token.");
+				<%
+				}
+				%>
+				// Exchange the auth code for an access token and refesh token
+				if (flow_<%=cid%> == null) {
+					flow_<%=cid%> = new com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow.Builder(new com.google.api.client.http.javanet.NetHttpTransport(),
+							new com.google.api.client.json.jackson2.JacksonFactory(), clientSecrets_<%=cid%>, SCOPES_<%=cid%>)
+							.setAccessType("offline").setApprovalPrompt("force")
+							.build();
+				}
+				com.google.api.client.googleapis.auth.oauth2.GoogleTokenResponse response_<%=cid%> = flow_<%=cid%>.newTokenRequest(authorizationCode_<%=cid%>).setRedirectUri(REDIRECT_URI_<%=cid%>).execute();
+				com.google.api.client.auth.oauth2.Credential credential_<%=cid%> = flow_<%=cid%>.createAndStoreCredential(response_<%=cid%>, null);
+	
+				<%
+				if(isLog4jEnabled){
+				%>
+					log.info("<%=cid%> - Store the refresh token for future use.");
+				<%
+				}
+				%>
+				// Store the refresh token for future use.
+				java.util.Properties storeProperties_<%=cid%> = new java.util.Properties();
+				storeProperties_<%=cid%>.setProperty("refreshtoken", credential_<%=cid%>.getRefreshToken());
+				java.io.FileOutputStream outputStream_<%=cid%> = new java.io.FileOutputStream(tokenFile_<%=cid %>);
+				storeProperties_<%=cid%>.store(outputStream_<%=cid%>,null);
+				if (outputStream_<%=cid%> != null) {
+				    outputStream_<%=cid%>.close();
+				}
+	
+				bigqueryclient_<%=cid%> = new com.google.api.services.bigquery.Bigquery.Builder(new com.google.api.client.http.javanet.NetHttpTransport(),new com.google.api.client.json.jackson2.JacksonFactory(),credential_<%=cid%>).build();
+			}
+		}
+	
+	
+		<%
+		String resultSizeType = ElementParameterParser.getValue(node,"__RESULT_SIZE__");
+		%>
+		<%@ include file="@{org.talend.designer.components.localprovider}/components/tBigQueryInput/BigQueryInputQueryHelper.javajet"%>	
+	
+		// Start a Query Job
+		String querySql_<%=cid %> = <%=query %>;
+		System.out.format("Running Query : %s\n", querySql_<%=cid %>);
+		<%
+		if(isLog4jEnabled){
+		%>
+			log.debug("<%=cid%> - Running Query: "+querySql_<%=cid %>);
+		<%
+		}
+		%>
+		
+		BigQueryUtil_<%=cid%> bigQueryUtil_<%=cid%> = new BigQueryUtil_<%=cid%>(PROJECT_ID_<%=cid%>, bigqueryclient_<%=cid%>, tokenFile_<%=cid%>);
+		
+		<%
+		if(isLog4jEnabled){
+		%>
+			log.info("<%=cid%> - Try <%="LARGE".equals(resultSizeType) ? "with" : "without"%> allow large results flag");
+		<%
+		}
+		%>
+		com.google.api.services.bigquery.model.Job insert_<%=cid %> = bigQueryUtil_<%=cid%>.executeQuery(querySql_<%=cid%>, <%="LARGE".equals(resultSizeType) ? true : false%>);
+	
+		<%
+		if(isLog4jEnabled){
+		%>
+			log.info("<%=cid%> - Retrieving records from dataset.");
+		<%
+		}
+		%>
+		String pageToken_<%=cid%> = null;
+	<%
+	} 
+	
+	// Service account authentication
+	else if (authMode.equals("SERVICEACCOUNT")) {
+	%>
+		com.google.auth.oauth2.GoogleCredentials credentials_<%=cid%>;
+		java.io.File credentialsFile_<%=cid%> = new java.io.File(<%=credentialsFile%>);
+		try(java.io.FileInputStream credentialsStream_<%=cid%> = new java.io.FileInputStream(credentialsFile_<%=cid%>)) {
+		    credentials_<%=cid%> = com.google.auth.oauth2.ServiceAccountCredentials.fromStream(credentialsStream_<%=cid%>);
+		}
+
+		com.google.cloud.bigquery.BigQuery bigquery_<%=cid%> = com.google.cloud.bigquery.BigQueryOptions.newBuilder()
+				.setCredentials(credentials_<%=cid%>)
+				.setProjectId(<%=projectId%>)
+				.build()
+				.getService();
+				
+		com.google.cloud.bigquery.QueryJobConfiguration queryConfiguration_<%=cid%> = com.google.cloud.bigquery.QueryJobConfiguration.newBuilder(<%=query%>).build();
+		com.google.cloud.bigquery.JobId jobId_tBigQueryInput_1 = com.google.cloud.bigquery.JobId.of(java.util.UUID.randomUUID().toString());
+		com.google.cloud.bigquery.Job job_<%=cid%> = bigquery_<%=cid%>.create(com.google.cloud.bigquery.JobInfo.newBuilder(queryConfiguration_<%=cid%>).setJobId(jobId_<%=cid%>).build());
+
+		<%
+		if(isLog4jEnabled){
+		%>
+			log.info("<%=cid%> - Sending job " + jobId_<%=cid%> + " with query: " + <%=query%>);
+		<%
+		}
+		%>
+		job_<%=cid%> = job_<%=cid%>.waitFor();
+
+		if (job_<%=cid%> == null) {
+			throw new RuntimeException("Job no longer exists");
+		} else if (job_<%=cid%>.getStatus().getError() != null) {
+			throw new RuntimeException(job_<%=cid%>.getStatus().getError().toString());
+		}
+
+		<%
+		if(isLog4jEnabled){
+		%>
+			log.info("<%=cid%> - Job " + jobId_<%=cid%> + " finished successfully.");
+		<%
+		}
+		%>
+		
+		com.google.cloud.bigquery.TableResult result_<%=cid%> = job_<%=cid%>.getQueryResults();
+
+		long nb_line_<%=cid%> = 0;
+	<%
+	} else {
+		throw new IllegalArgumentException("authentication mode should be either \"SERVICEACCOUNT\" or \"OAUTH\", but it is " + authMode);
+	}
+	
+	// Result fetching part
+	
+	if (authMode.equals("OAUTH")) {
+	%>
+	while (true) {
+		// Fetch Results
+		com.google.api.services.bigquery.model.TableDataList dataList_<%=cid %> = bigqueryclient_<%=cid%>.tabledata()
+					.list(PROJECT_ID_<%=cid %>,
+							insert_<%=cid %>.getConfiguration().getQuery()
+									.getDestinationTable().getDatasetId(),
+							insert_<%=cid %>.getConfiguration().getQuery()
+									.getDestinationTable().getTableId())
+					.setPageToken(pageToken_<%=cid%>).execute();
+		
+		List<com.google.api.services.bigquery.model.TableRow> rows_<%=cid %> = dataList_<%=cid %>.getRows();
+
+		if(rows_<%=cid %> == null) {
+			// Means there is no record.
+			rows_<%=cid %> = new java.util.ArrayList<com.google.api.services.bigquery.model.TableRow>();
+		}
+		
+		for (com.google.api.services.bigquery.model.TableRow row_<%=cid %> : rows_<%=cid %>) {
+			java.util.List<com.google.api.services.bigquery.model.TableCell> field_<%=cid %> = row_<%=cid %>.getF();
+			Object value_<%=cid%> = null;
+			nb_line_<%=cid%> ++;
+		<%
+	} 
+	
+	
+	
+	else if (authMode.equals("SERVICEACCOUNT")) {
+	%>
+		for (com.google.cloud.bigquery.FieldValueList field_<%=cid %> : result_<%=cid%>.iterateAll()) {
+			Object value_<%=cid%>;
+			nb_line_<%=cid%> ++;
+	<%
+	} else {
+		throw new IllegalArgumentException("authentication mode should be either \"SERVICEACCOUNT\" or \"OAUTH\", but it is " + authMode);
+	}
+	
+	
+		// Send data to output connectors
+		List< ? extends IConnection> conns = node.getOutgoingSortedConnections();
+		if (conns != null){
+			if (conns.size()>0){
+				IConnection conn =conns.get(0);
+				String connName = conn.getName();
+				if (conn.getLineStyle().hasConnectionCategory(IConnectionCategory.DATA)) {
+					List<IMetadataTable> metadatas = node.getMetadataList();
+					if ((metadatas!=null) && (metadatas.size() > 0)) {
+						IMetadataTable metadata = metadatas.get(0);
+						if (metadata != null) {
+							String encoding = ElementParameterParser.getValue(node,"__ENCODING__");
+							String advancedSeparatorStr = ElementParameterParser.getValue(node, "__ADVANCED_SEPARATOR__");
+							boolean advancedSeparator = (advancedSeparatorStr!=null&&!("").equals(advancedSeparatorStr))?("true").equals(advancedSeparatorStr):false;
+							String thousandsSeparator = ElementParameterParser.getValueWithJavaType(node, "__THOUSANDS_SEPARATOR__", JavaTypesManager.CHARACTER);
+							String decimalSeparator = ElementParameterParser.getValueWithJavaType(node, "__DECIMAL_SEPARATOR__", JavaTypesManager.CHARACTER);
+						
+							List<IMetadataColumn> columns = metadata.getListColumns();
+							int nbColumns = columns.size();
+							for (int i = 0; i < nbColumns; i++ ) {
+								IMetadataColumn column = columns.get(i);
+								String columnName = column.getLabel();
+							
+								String typeToGenerate = JavaTypesManager.getTypeToGenerate(column.getTalendType(), column.isNullable());
+								JavaType javaType = JavaTypesManager.getJavaTypeFromId(column.getTalendType());
+								String patternValue = column.getPattern() == null || column.getPattern().trim().length() == 0 ? null : column.getPattern(); 
+								if (authMode.equals("OAUTH")) {
+								%>
+								value_<%=cid%> = field_<%=cid %>.get(<%=i%>).getV();
+								<%
+								} else if (authMode.equals("SERVICEACCOUNT")) {
+								%>
+								value_<%=cid%> = field_<%=cid %>.get(<%=i%>).getValue();
+								<%
+								} else {
+									throw new IllegalArgumentException("authentication mode should be either \"SERVICEACCOUNT\" or \"OAUTH\", but it is " + authMode);
+								}
+								%>
+								if(com.google.api.client.util.Data.isNull(value_<%=cid%>)) value_<%=cid%> = null;
+								if(value_<%=cid%> != null){
+									
+									<%
+									if (javaType == JavaTypesManager.STRING) {
+									%>
+										<%=connName%>.<%=columnName%> = value_<%=cid%>.toString();
+									<%
+									} else if (javaType == JavaTypesManager.OBJECT) {
+									%>		
+										<%=connName%>.<%=columnName%> = value_<%=cid%>;
+									<%		
+									} else if(javaType == JavaTypesManager.DATE) {						
+									%>					
+										<%=connName%>.<%=columnName%> = ParserUtils.parseTo_Date(value_<%=cid%>.toString());				
+									<%		
+									} else if(advancedSeparator && JavaTypesManager.isNumberType(javaType)) { 
+									%>
+										<%=connName%>.<%=columnName%> = ParserUtils.parseTo_<%= typeToGenerate %>(ParserUtils.parseTo_Number(value_<%=cid%>.toString(), <%= thousandsSeparator %>, <%= decimalSeparator %>));
+									<%
+									} else if(javaType == JavaTypesManager.BYTE_ARRAY) { 
+									%>							
+										<%=connName%>.<%=columnName%> = value_<%=cid%>.toString().getBytes(<%=encoding %>);
+									<%
+									} else {
+									%>
+										<%=connName%>.<%=columnName%> = ParserUtils.parseTo_<%= typeToGenerate %>(value_<%=cid%>.toString());
+									<%
+									}
+									%>
+								}else{
+									<%=connName%>.<%=columnName%> = <%=JavaTypesManager.getDefaultValueFromJavaType(typeToGenerate, column.getDefault())%>;
+								}
+							<%
+							}
+							log4jFileUtil.debugRetriveData(node);
+						}
+					}
+				}
+			}
+		}
+		%>

main/plugins/org.talend.designer.components.localprovider/components/tBigQueryRow/tBigQueryRow_end.javajet

@@ -0,0 +1,32 @@
+<%@ jet 
+imports="
+        org.talend.core.model.process.INode 
+        org.talend.designer.codegen.config.CodeGeneratorArgument
+		org.talend.core.model.process.ElementParameterParser 
+" 
+%>
+	<%@ include file="@{org.talend.designer.components.localprovider}/components/templates/Log4j/Log4jFileUtil.javajet"%>
+	<%
+    CodeGeneratorArgument codeGenArgument = (CodeGeneratorArgument) argument;
+    INode node = (INode)codeGenArgument.getArgument();
+    String cid = node.getUniqueName();
+	String authMode = ElementParameterParser.getValue(node,"__AUTH_MODE__");
+	if (authMode.equals("OAUTH")) {
+	%>
+			}
+		pageToken_<%=cid%> = dataList_<%=cid %>.getPageToken();
+		if (null == pageToken_<%=cid%>) {
+			break;
+		}
+	}
+	bigQueryUtil_<%=cid%>.cleanup();
+  	<%
+  	} else if (authMode.equals("SERVICEACCOUNT")) {
+  	%>
+  		}
+  	<%
+  	} else {
+		throw new IllegalArgumentException("authentication mode should be either \"SERVICEACCOUNT\" or \"OAUTH\", but it is " + authMode);
+  	}
+	log4jFileUtil.retrievedDataNumberInfo(node);
+	%>

main/plugins/org.talend.designer.components.localprovider/components/tBigQueryRow/tBigQueryRow_java.xml

@@ -0,0 +1,179 @@
+<COMPONENT>
+  <HEADER
+    PLATEFORM="ALL"
+    SERIAL=""
+    VERSION="0.102"
+    STATUS="ALPHA"
+
+    COMPATIBILITY="ALL"
+    AUTHOR="Talend"
+    RELEASE_DATE="20070312A"
+    STARTABLE="true"
+    LOG4J_ENABLED="true"
+  >
+    <SIGNATURE/>
+  </HEADER>
+  <FAMILIES>
+    <FAMILY>Big Data/Google BigQuery</FAMILY>
+  </FAMILIES>
+  <DOCUMENTATION>
+    <URL/>
+  </DOCUMENTATION>
+  <CONNECTORS>
+    <CONNECTOR CTYPE="FLOW" MAX_INPUT="0" MAX_OUTPUT="1"/>
+    <CONNECTOR CTYPE="ITERATE" MAX_OUTPUT="1" MAX_INPUT="1"/>
+    <CONNECTOR CTYPE="SUBJOB_OK" MAX_INPUT="1" />
+    <CONNECTOR CTYPE="SUBJOB_ERROR" MAX_INPUT="1" />
+    <CONNECTOR CTYPE="COMPONENT_OK"/>
+    <CONNECTOR CTYPE="COMPONENT_ERROR"/>
+    <CONNECTOR CTYPE="RUN_IF"/>
+  </CONNECTORS>
+  <PARAMETERS>
+    <PARAMETER
+    	NAME="SCHEMA"
+    	FIELD="SCHEMA_TYPE"
+    	REQUIRED="true"
+    	NUM_ROW="1"
+    />
+    
+    <PARAMETER NAME="AUTH_MODE" FIELD="CLOSED_LIST" NUM_ROW="3" REQUIRED="true" GROUP="AUTHENTICATION" REPOSITORY_VALUE="AUTH_MODE">
+			<ITEMS DEFAULT="SERVICEACCOUNT">
+				<ITEM NAME="SERVICEACCOUNT" VALUE="SERVICEACCOUNT" />
+				<ITEM NAME="OAUTH" VALUE="OAUTH" />
+			</ITEMS>
+	</PARAMETER>
+	
+	<PARAMETER 
+			NAME="SERVICE_ACCOUNT_CREDENTIALS_FILE" 
+			REPOSITORY_VALUE="SERVICE_ACCOUNT_CREDENTIALS_FILE"
+			GROUP="AUTHENTICATION"
+			FIELD="FILE" 
+			NUM_ROW="10" 
+			REQUIRED="true" 
+			SHOW_IF="AUTH_MODE == 'SERVICEACCOUNT'"
+			>
+			<DEFAULT>""</DEFAULT>
+	</PARAMETER>
+
+    <PARAMETER
+      NAME="CLIENT_ID"
+      FIELD="TEXT"
+      NUM_ROW="10"
+      REQUIRED="true"
+      SHOW_IF="AUTH_MODE == 'OAUTH'"
+      GROUP="AUTHENTICATION" REPOSITORY_VALUE="CLIENT_ID"
+    >
+      <DEFAULT>""</DEFAULT>
+    </PARAMETER>
+
+	<PARAMETER
+      NAME="CLIENT_SECRET"
+      FIELD="PASSWORD"
+      NUM_ROW="20"
+      REQUIRED="true"
+      SHOW_IF="AUTH_MODE == 'OAUTH'"
+      GROUP="AUTHENTICATION" REPOSITORY_VALUE="CLIENT_SECRET"
+    >
+      <DEFAULT>""</DEFAULT>
+    </PARAMETER>
+
+	<PARAMETER
+      NAME="PROJECT_ID"
+      FIELD="TEXT"
+      NUM_ROW="40"
+      REQUIRED="true"
+    >
+      <DEFAULT>""</DEFAULT>
+    </PARAMETER>
+
+	<PARAMETER
+      NAME="AUTHORIZATION_CODE"
+      FIELD="TEXT"
+      NUM_ROW="30"
+      REQUIRED="true"
+      SHOW_IF="AUTH_MODE == 'OAUTH'"
+      GROUP="AUTHENTICATION" REPOSITORY_VALUE="AUTHORIZATION_CODE"
+    >
+      <DEFAULT>""</DEFAULT>
+    </PARAMETER>
+    <PARAMETER
+      NAME="QUERY"
+      FIELD="MEMO_SQL"
+      NUM_ROW="50"
+      REQUIRED="true"
+    >
+      <DEFAULT>"select id, name from employee"</DEFAULT>
+    </PARAMETER>
+    <!-- 
+    https://cloud.google.com/bigquery/querying-data#large-results 
+    SMALL: allowLargeResult is false
+    LARGE: allowLargeResult is true and auto create/clean temp dataset/table
+    AUTO: try SMALL first, if get responseTooLarge error then try LARGE
+    -->
+    <PARAMETER 
+   		NAME="RESULT_SIZE"
+   		FIELD="CLOSED_LIST"
+		NUM_ROW="60"
+		REQUIRED="true"
+		SHOW_IF="AUTH_MODE == 'OAUTH'"
+    >
+      	<ITEMS DEFAULT="SMALL">
+			<ITEM NAME="SMALL" VALUE="SMALL" />
+			<ITEM NAME="LARGE" VALUE="LARGE" />
+			<ITEM NAME="AUTO" VALUE="AUTO" />
+		</ITEMS>
+    </PARAMETER>
+   </PARAMETERS>
+   <ADVANCED_PARAMETERS>
+    <PARAMETER NAME="TOKEN_NAME" FIELD="FILE" NUM_ROW="5"
+        REQUIRED="true" SHOW_IF="AUTH_MODE == 'OAUTH'">
+        <DEFAULT>"__COMP_DEFAULT_FILE_DIR__/token.properties"</DEFAULT>
+    </PARAMETER>
+
+   	<PARAMETER NAME="ADVANCED_SEPARATOR" FIELD="CHECK" REQUIRED="true" NUM_ROW="10" >
+		<DEFAULT>false</DEFAULT>
+	</PARAMETER>
+
+	<PARAMETER NAME="THOUSANDS_SEPARATOR" FIELD="TEXT" REQUIRED="true" NUM_ROW="10"
+		SHOW_IF="(ADVANCED_SEPARATOR == 'true')">
+		<DEFAULT>","</DEFAULT>
+	</PARAMETER>
+
+	<PARAMETER NAME="DECIMAL_SEPARATOR" FIELD="TEXT" REQUIRED="true" NUM_ROW="10"
+		SHOW_IF="(ADVANCED_SEPARATOR == 'true')">
+		<DEFAULT>"."</DEFAULT>
+	</PARAMETER>
+
+	<PARAMETER NAME="ENCODING" FIELD="ENCODING_TYPE" NUM_ROW="20"
+		REQUIRED="true" REPOSITORY_VALUE="ENCODING">
+		<DEFAULT>"ISO-8859-15"</DEFAULT>
+	</PARAMETER>
+   </ADVANCED_PARAMETERS>
+   <CODEGENERATION>
+     <IMPORTS>
+		<IMPORT NAME="google-api-client-1.19.0.jar" MODULE="google-api-client-1.19.0.jar" MVN="mvn:org.talend.libraries/google-api-client-1.19.0/6.0.0"  UrlPath="platform:/plugin/org.talend.libraries.apache.google/lib/google-api-client-1.19.0.jar" REQUIRED="true" />
+		<IMPORT NAME="google-api-services-oauth2-v2-rev78-1.19.0.jar" MODULE="google-api-services-oauth2-v2-rev78-1.19.0.jar" MVN="mvn:org.talend.libraries/google-api-services-oauth2-v2-rev78-1.19.0/6.0.0"  UrlPath="platform:/plugin/org.talend.libraries.apache.google/lib/google-api-services-oauth2-v2-rev78-1.19.0.jar" REQUIRED="true" />
+		<IMPORT NAME="google-api-services-bigquery-v2-rev391-1.21.0.jar" MODULE="google-api-services-bigquery-v2-rev391-1.21.0.jar" MVN="mvn:org.talend.libraries/google-api-services-bigquery-v2-rev391-1.21.0/6.0.0"  UrlPath="platform:/plugin/org.talend.libraries.apache.google/lib/google-api-services-bigquery-v2-rev391-1.21.0.jar" REQUIRED="true" />
+		<IMPORT NAME="google-http-client-1.19.0.jar" MODULE="google-http-client-1.19.0.jar" MVN="mvn:org.talend.libraries/google-http-client-1.19.0/6.0.0"  UrlPath="platform:/plugin/org.talend.libraries.apache.google/lib/google-http-client-1.19.0.jar" REQUIRED="true" />
+		<IMPORT NAME="google-oauth-client-1.19.0.jar" MODULE="google-oauth-client-1.19.0.jar" MVN="mvn:org.talend.libraries/google-oauth-client-1.19.0/6.0.0"  UrlPath="platform:/plugin/org.talend.libraries.apache.google/lib/google-oauth-client-1.19.0.jar" REQUIRED="true" />
+		<IMPORT NAME="google-http-client-jackson2-1.19.0.jar" MODULE="google-http-client-jackson2-1.19.0.jar" MVN="mvn:org.talend.libraries/google-http-client-jackson2-1.19.0/6.0.0"  UrlPath="platform:/plugin/org.talend.libraries.apache.google/lib/google-http-client-jackson2-1.19.0.jar" REQUIRED="true" />
+		<IMPORT NAME="guava-jdk5-13.0.jar" MODULE="guava-jdk5-13.0.jar" MVN="mvn:org.talend.libraries/guava-jdk5-13.0/6.0.0"  UrlPath="platform:/plugin/org.talend.libraries.guava/lib/guava-jdk5-13.0.jar" REQUIRED="true" />
+		<IMPORT NAME="jackson-core-2.9.4.jar" MODULE="jackson-core-2.9.4.jar" MVN="mvn:com.fasterxml.jackson.core/jackson-core/2.9.4"  UrlPath="platform:/plugin/org.talend.libraries.jackson/lib/jackson-core-2.9.4.jar" REQUIRED="true" />
+		<IMPORT NAME="google-cloud-bigquery-1.32.0.jar" MODULE="google-cloud-bigquery-1.32.0.jar" MVN="mvn:com.google.cloud/google-cloud-bigquery/1.32.0" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="google-http-client-jackson-1.23.0.jar" MODULE="google-http-client-jackson-1.23.0.jar" MVN="mvn:com.google.http-client/google-http-client-jackson/1.23.0" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="threetenbp-1.3.3.jar" MODULE="threetenbp-1.3.3.jar" MVN="mvn:org.threeten/threetenbp/1.3.3" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="google-auth-library-credentials-0.9.1.jar" MODULE="google-auth-library-credentials-0.9.1.jar" MVN="mvn:com.google.auth/google-auth-library-credentials/0.9.1" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="gax-httpjson-0.44.0.jar" MODULE="gax-httpjson-0.44.0.jar" MVN="mvn:com.google.api/gax-httpjson/0.44.0" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="jackson-core-asl-1.9.11.jar" MODULE="jackson-core-asl-1.9.11.jar" MVN="mvn:org.codehaus.jackson/jackson-core-asl/1.9.11" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="google-auth-library-oauth2-http-0.9.1.jar" MODULE="google-auth-library-oauth2-http-0.9.1.jar" MVN="mvn:com.google.auth/google-auth-library-oauth2-http/0.9.1" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="google-cloud-core-1.32.0.jar" MODULE="google-cloud-core-1.32.0.jar" MVN="mvn:com.google.cloud/google-cloud-core/1.32.0" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="google-cloud-core-http-1.32.0.jar" MODULE="google-cloud-core-http-1.32.0.jar" MVN="mvn:com.google.cloud/google-cloud-core-http/1.32.0" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="guava-20.0.jar" MODULE="guava-20.0.jar" MVN="mvn:com.google.guava/guava/20.0" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="gax-1.27.0.jar" MODULE="gax-1.27.0.jar" MVN="mvn:com.google.api/gax/1.27.0" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="google-http-client-appengine-1.23.0.jar" MODULE="google-http-client-appengine-1.23.0.jar" MVN="mvn:com.google.http-client/google-http-client-appengine/1.23.0" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+		<IMPORT NAME="api-common-1.6.0.jar" MODULE="api-common-1.6.0.jar" MVN="mvn:com.google.api/api-common/1.6.0" REQUIRED_IF="AUTH_MODE == 'SERVICEACCOUNT'" />
+     </IMPORTS>
+   </CODEGENERATION>
+   <RETURNS>
+   </RETURNS>
+</COMPONENT>

main/plugins/org.talend.designer.components.localprovider/components/tBigQueryRow/tBigQueryRow_messages.properties

@@ -0,0 +1,29 @@
+#Created by JInto - www.guh-software.de
+#Wed Mar 19 09:39:53 CST 2008
+LONG_NAME=Connect and run a query on Google BigQuery 
+HELP=org.talend.help.tBigQueryInput
+
+AUTHENTICATION.NAME=Authentication
+SERVICE_ACCOUNT_CREDENTIALS_FILE.NAME=Service account credentials file
+AUTH_MODE.NAME=Authentication mode
+AUTH_MODE.ITEM.SERVICEACCOUNT=Service account
+AUTH_MODE.ITEM.OAUTH=OAuth 2.0
+
+CLIENT_ID.NAME=Client Id
+CLIENT_SECRET.NAME=Client Secret
+PROJECT_ID.NAME=Project Id
+AUTHORIZATION_CODE.NAME=Authorization Code
+QUERY.NAME=Query
+SCHEMA.NAME=Schema
+
+ENCODING.NAME=Encoding
+ADVANCED_SEPARATOR.NAME=Advanced separator(for number)
+DECIMAL_SEPARATOR.NAME=Decimal separator
+THOUSANDS_SEPARATOR.NAME=Thousands separator
+
+TOKEN_NAME.NAME=token properties File Name
+
+RESULT_SIZE.NAME=Result size
+RESULT_SIZE.ITEM.SMALL=Small(without allowLargeResults)
+RESULT_SIZE.ITEM.LARGE=Large(with allowLargeResults)
+RESULT_SIZE.ITEM.AUTO=Auto