Update PATCH_RELEASE_NOTE.md

v3

PATCH_RELEASE_NOTE.md

@@ -0,0 +1,34 @@
+---
+version: 7.1.1
+module: https://talend.poolparty.biz/coretaxonomy/42
+product: https://talend.poolparty.biz/coretaxonomy/22
+---
+# TPS-3161
+| Info             | Value |
+| ---------------- | ---------------- |
+| Patch Name       | patch\_20190823\_TPS-3161\_v3|
+| Release Date     | 2019-08-23 |
+| Target Version   | 20181026\_1147-V7.1.1 |
+| Product affected | Talend Studio |
+## Introduction <!-- mandatory -->
+This is a self-contained patch.
+**NOTE**: For information on how to obtain this patch, reach out to your Support contact at Talend.
+## Fixed issues <!-- mandatory -->
+This patch contains the following fixes:
+- TPS-3161 resolves issue on tBigQueryOutput (TBD-8708)
+## Prerequisites <!-- mandatory -->
+Consider the following requirements for your system:
+- Talend Studio 7.1.1 must be installed.
+## Installation <!-- mandatory -->
+### Installing the patch using Software update <!-- if applicable -->
+1) Logon TAC and switch to Configuration->Software Update, then enter the correct values and save referring to the documentation: https://help.talend.com/reader/f7Em9WV_cPm2RRywucSN0Q/j9x5iXV~vyxMlUafnDejaQ
+2) Switch to Software update page, where the new patch will be listed. The patch can be downloaded from here into the nexus repository.
+3) On Studio Side: Logon Studio with remote mode, on the logon page the Update button is displayed: click this button to install the patch.
+### Installing the patch using Talend Studio <!-- if applicable -->
+1) Create a folder named "patches" under your studio installer directory and copy the patch .zip file to this folder.
+2) Restart your studio: a window pops up, then click OK to install the patch, or restart the commandline and the patch will be installed automatically.
+### Installing the patch using Commandline <!-- if applicable -->
+Execute the following commands:
+1. Talend-Studio-win-x86_64.exe -nosplash -application org.talend.commandline.CommandLine -consoleLog -data commandline-workspace startServer -p 8002 --talendDebug
+2. initRemote {tac_url} -ul {TAC login username} -up {TAC login password}
+3. checkAndUpdate -tu {TAC login username} -tup {TAC login password}

main/plugins/org.talend.designer.components.localprovider/components/tBigQueryBulkExec/tBigQueryBulkExec_begin.javajet

@@ -24,7 +24,7 @@
 	String dataset = ElementParameterParser.getValue(node, "__DATASET__");
 	String table = ElementParameterParser.getValue(node, "__TABLE__");
 	String gsFile = ElementParameterParser.getValue(node, "__GS_FILE__");
-
+	String fieldDelimiter = ElementParameterParser.getValue(node, "__FIELD_DELIMITER__");
 	String actionOnData = ElementParameterParser.getValue(node, "__ACTION_ON_DATA__");
 	boolean dieOnError = "true".equals(ElementParameterParser.getValue(node, "__DIE_ON_ERROR__"));
 
@@ -279,24 +279,44 @@
 					IMetadataColumn column = columns.get(i);
 					String columnName = column.getLabel();
 					String typeToGenerate = "string";
-					if("id_Float".equals(column.getTalendType())) {
+					if("id_Float".equals(column.getTalendType()) || "id_Double".equals(column.getTalendType())) {
 						typeToGenerate = "float";
-					} else if("id_Integer".equals(column.getTalendType())) {
+					}else if("id_Integer".equals(column.getTalendType()) || "id_Long".equals(column.getTalendType()) || "id_Short".equals(column.getTalendType())) {
 						typeToGenerate = "integer";
+					} else if("id_Character".equals(column.getTalendType())) {
+						typeToGenerate = "string";
+					} else if("id_BigDecimal".equals(column.getTalendType())) {
+						typeToGenerate = "numeric";					
 					} else if("id_Boolean".equals(column.getTalendType())) {
 						typeToGenerate = "boolean";
 					} else if("id_Date".equals(column.getTalendType())) {
-						typeToGenerate = "timestamp";
+						String pattern = column.getPattern();
+					    if(pattern.length() == 12 || pattern.isEmpty() || "\"\"".equals(pattern)) {
+					        typeToGenerate = "date";
+					    }else if(pattern.length() > 12){
+					        typeToGenerate = "timestamp";
+					    }else{
+					        typeToGenerate = "string";
+					    }
 					}
 					%>
+					<%
+						String modeType = null;
+						if (!column.isNullable()) { 
+							modeType = "REQUIRED";
+						} else {
+							modeType = "NULLABLE";
+						}
+					%>
 					com.google.api.services.bigquery.model.TableFieldSchema <%=columnName%>_<%=cid%> = new com.google.api.services.bigquery.model.TableFieldSchema();
 					<%=columnName%>_<%=cid%>.setName("<%=columnName%>");
 					<%=columnName%>_<%=cid%>.setType("<%=typeToGenerate%>");
+					<%=columnName%>_<%=cid%>.setMode("<%=modeType%>");
 					fields_<%=cid%>.add(<%=columnName%>_<%=cid%>);		
 					<%
 					if(isLog4jEnabled){
 					%>
-						log.debug("<%=cid%> - Field index[<%=i%>] {\"name\":\"<%=columnName%>\",\"type\":\"<%=typeToGenerate%>\"}");
+						log.debug("<%=cid%> - Field index[<%=i%>] {\"name\":\"<%=columnName%>\",\"type\":\"<%=typeToGenerate%>\",\"mode\":\"<%=modeType%>\"}");
 					<%
 					}
 				}
@@ -321,7 +341,7 @@
 	
 		if("true".equals(ElementParameterParser.getValue(node, "__SET_FIELD_DELIMITER__"))) {
 		%>
-			queryLoad_<%=cid%>.setFieldDelimiter(<%=ElementParameterParser.getValue(node, "__FIELD_DELIMITER__")%>);
+			queryLoad_<%=cid%>.setFieldDelimiter(<%=fieldDelimiter%>);
 		<%
 		}
 		%>
@@ -336,7 +356,7 @@
 		queryLoad_<%=cid%>.setDestinationTable(destinationTable_<%=cid%>);
 		queryLoad_<%=cid%>.setSourceUris(java.util.Arrays.asList(<%=ElementParameterParser.getValue(node, "__GS_FILE__")%>));
 		queryLoad_<%=cid%>.setSkipLeadingRows(<%=ElementParameterParser.getValue(node, "__GS_FILE_HEADER__")%>);
-	
+		queryLoad_<%=cid%>.setNullMarker("\\N");
 		config_<%=cid%>.setLoad(queryLoad_<%=cid%>);
 		
 		job_<%=cid%>.setConfiguration(config_<%=cid%>);
@@ -375,10 +395,10 @@
 		}
 		if (jobExec_<%=cid%>.getStatus().getState().equals("RUNNING")
 				|| jobExec_<%=cid%>.getStatus().getState().equals("PENDING")) {
-			com.google.api.services.bigquery.model.Job pollJob_<%=cid%> = bigqueryclient_<%=cid%>.jobs().get(PROJECT_ID_<%=cid%>,jobExec_<%=cid%>.getJobReference().getJobId()).execute();
+            		com.google.api.services.bigquery.model.Job pollJob_<%=cid%> = bigqueryclient_<%=cid%>.jobs().get(PROJECT_ID_<%=cid%>,jobExec_<%=cid%>.getJobReference().getJobId()).setLocation(jobExec_<%=cid%>.getJobReference().getLocation()).execute();
 			while (pollJob_<%=cid%>.getStatus().getState().equals("RUNNING") || pollJob_<%=cid%>.getStatus().getState().equals("PENDING")) {
 				Thread.sleep(1000);
-				pollJob_<%=cid%> = bigqueryclient_<%=cid%>.jobs().get(PROJECT_ID_<%=cid%>,jobExec_<%=cid%>.getJobReference().getJobId()).execute();
+				pollJob_<%=cid%> = bigqueryclient_<%=cid%>.jobs().get(PROJECT_ID_<%=cid%>,jobExec_<%=cid%>.getJobReference().getJobId()).setLocation(jobExec_<%=cid%>.getJobReference().getLocation()).execute();
 				System.out.println(String.format(
 						"Waiting on job %s ... Current status: %s", jobExec_<%=cid%>
 								.getJobReference().getJobId(), pollJob_<%=cid%>
@@ -440,6 +460,7 @@
 		/* ----START-CREATING-JOB (Cloud API)---- */
 		com.google.cloud.bigquery.TableId tableId_<%=cid%> = com.google.cloud.bigquery.TableId.of(<%=projectId%>, <%=dataset%>, <%=table%>);
 		com.google.cloud.bigquery.Table table_<%=cid%> = bigquery_<%=cid%>.getTable(tableId_<%=cid%>);
+		com.google.cloud.bigquery.LoadJobConfiguration.Builder loadJobBuilder_<%=cid%> = com.google.cloud.bigquery.LoadJobConfiguration.newBuilder(tableId_<%=cid%>, <%=gsFile%>);
 		if (<%=ElementParameterParser.getBooleanValue(node, "__DROP__")%> && table_<%=cid%> != null) {
 			boolean deleted = bigquery_<%=cid%>.delete(tableId_<%=cid%>);	
 			if (deleted) {
@@ -454,9 +475,7 @@
 				throw new RuntimeException("Unable to delete table " + tableId_<%=cid%>);
 			}
 		}
-		boolean tableNotExist = table_<%=cid%> == null;
-		if (<%=ElementParameterParser.getBooleanValue(node, "__DROP__")%> || tableNotExist) {
-			boolean typeSupported = true;
+		if (<%=ElementParameterParser.getBooleanValue(node, "__DROP__")%> || <%=ElementParameterParser.getBooleanValue(node, "__CREATE_TABLE_IF_NOT_EXIST__")%>) {
 			java.util.List<com.google.cloud.bigquery.Field> fields_<%=cid%> = new java.util.ArrayList<>();
 			<%
 			List<IMetadataTable> metadatas = node.getMetadataList();
@@ -468,27 +487,37 @@
 					for (int i = 0; i < nbColumns; i++ ) {
 						IMetadataColumn column = columns.get(i);
 						String columnName = column.getLabel();
-						String typeToGenerate = null;
-						if("id_String".equals(column.getTalendType())) {
+						String typeToGenerate = "com.google.cloud.bigquery.LegacySQLTypeName.STRING";
+						if("id_String".equals(column.getTalendType()) || "id_Character".equals(column.getTalendType())) {
 							typeToGenerate = "com.google.cloud.bigquery.LegacySQLTypeName.STRING";
 						} else if ("id_Float".equals(column.getTalendType()) || "id_Double".equals(column.getTalendType())) {
 							typeToGenerate = "com.google.cloud.bigquery.LegacySQLTypeName.FLOAT";
 						} else if ("id_Short".equals(column.getTalendType()) || "id_Integer".equals(column.getTalendType()) || "id_Long".equals(column.getTalendType())) {
 							typeToGenerate = "com.google.cloud.bigquery.LegacySQLTypeName.INTEGER";
+						} else if ("id_BigDecimal".equals(column.getTalendType())) {
+							typeToGenerate = "com.google.cloud.bigquery.LegacySQLTypeName.NUMERIC";						
 						} else if ("id_Boolean".equals(column.getTalendType())) {
 							typeToGenerate = "com.google.cloud.bigquery.LegacySQLTypeName.BOOLEAN";
-						} else if ("id_Date".equals(column.getTalendType())) {
-							typeToGenerate = "com.google.cloud.bigquery.LegacySQLTypeName.TIMESTAMP";
-						} else {
-						%>
-							typeSupported = false;
-						<%
+						} else if ("id_Date".equals(column.getTalendType())) {							
+							String pattern = column.getPattern();
+						    if(pattern.length() == 12 || pattern.isEmpty() || "\"\"".equals(pattern)) {
+						        typeToGenerate = "com.google.cloud.bigquery.LegacySQLTypeName.DATE";
+						    }else if(pattern.length() > 12){
+						        typeToGenerate = "com.google.cloud.bigquery.LegacySQLTypeName.TIMESTAMP";
+						    }else{
+						        typeToGenerate = "com.google.cloud.bigquery.LegacySQLTypeName.STRING";
+						    }
+						}
+						
+						String modeType = "NULLABLE";
+						if (!column.isNullable()) { 
+							modeType = "REQUIRED";
 						}
 						%>
-						if (!typeSupported) {
-							throw new IllegalArgumentException("unsupported type for column [<%=columnName%>]: " + "<%=column.getTalendType()%>");
-						}
-						com.google.cloud.bigquery.Field field_<%=i%> = com.google.cloud.bigquery.Field.of("<%=columnName%>", <%=typeToGenerate%>);
+						
+						com.google.cloud.bigquery.Field field_<%=i%> = com.google.cloud.bigquery.Field.newBuilder("<%=columnName%>", <%=typeToGenerate%>)
+																									  .setMode(com.google.cloud.bigquery.Field.Mode.valueOf("<%=modeType%>"))
+																									  .build();
 						fields_<%=cid%>.add(field_<%=i%>);		
 						<%
 						if(isLog4jEnabled){
@@ -500,11 +529,56 @@
 				}
 			}
 			%>
+			
 			com.google.cloud.bigquery.Schema schema_<%=cid%> = com.google.cloud.bigquery.Schema.of(fields_<%=cid%>);
 			com.google.cloud.bigquery.TableInfo tableInfo_<%=cid%> = com.google.cloud.bigquery.TableInfo.newBuilder(tableId_<%=cid%>, com.google.cloud.bigquery.StandardTableDefinition.of(schema_<%=cid%>)).build();
 			table_<%=cid%> = bigquery_<%=cid%>.create(tableInfo_<%=cid%>);
+			loadJobBuilder_<%=cid%>.setSchema(schema_<%=cid%>);
 		}
-		com.google.cloud.bigquery.Job job_<%=cid%> = table_<%=cid%>.load(com.google.cloud.bigquery.FormatOptions.csv(), <%=gsFile%>);
+		
+			<%
+				if("true".equals(ElementParameterParser.getValue(node, "__CREATE_TABLE_IF_NOT_EXIST__"))) {
+			%>
+				loadJobBuilder_<%=cid%>.setCreateDisposition(com.google.cloud.bigquery.JobInfo.CreateDisposition.CREATE_IF_NEEDED);
+			<%
+			} else {
+			%>
+				loadJobBuilder_<%=cid%>.setCreateDisposition(com.google.cloud.bigquery.JobInfo.CreateDisposition.CREATE_NEVER);
+			<%
+			}
+			%>
+			
+			<%
+			if("APPEND".equals(actionOnData)) {
+			%>
+				loadJobBuilder_<%=cid%>.setWriteDisposition(com.google.cloud.bigquery.JobInfo.WriteDisposition.WRITE_APPEND);
+			<%
+			} else if("TRUNCATE".equals(actionOnData)) {
+			%>
+				loadJobBuilder_<%=cid%>.setWriteDisposition(com.google.cloud.bigquery.JobInfo.WriteDisposition.WRITE_TRUNCATE);
+				
+			<%
+			}
+		 	else {
+			%>
+				loadJobBuilder_<%=cid%>.setWriteDisposition(com.google.cloud.bigquery.JobInfo.WriteDisposition.WRITE_EMPTY);				
+			<%
+			}
+			%>
+		
+		loadJobBuilder_<%=cid%>.setDestinationTable(tableId_<%=cid%>);
+                com.google.cloud.bigquery.CsvOptions.Builder csvOptions_<%=cid%> = com.google.cloud.bigquery.CsvOptions.newBuilder();
+		csvOptions_<%=cid%>.setAllowQuotedNewLines(true);
+																				
+		<%if("true".equals(ElementParameterParser.getValue(node, "__SET_FIELD_DELIMITER__"))) {
+        %>
+         	loadJobBuilder_<%=cid%>.setFormatOptions(csvOptions_<%=cid%>.setFieldDelimiter(<%=fieldDelimiter%>).build());
+        <%
+        }
+        %>		
+		
+		loadJobBuilder_<%=cid%>.setNullMarker("\\N");																
+		com.google.cloud.bigquery.Job job_<%=cid%> = bigquery_<%=cid%>.create(com.google.cloud.bigquery.JobInfo.of(loadJobBuilder_<%=cid%>.build()));
 		job_<%=cid%> = job_<%=cid%>.waitFor(com.google.cloud.RetryOption.initialRetryDelay(org.threeten.bp.Duration.ofSeconds(1)), com.google.cloud.RetryOption.totalTimeout(org.threeten.bp.Duration.ofSeconds(30)));
 		if (job_<%=cid%> != null && job_<%=cid%>.getStatus().getError() == null) {
 			<%
@@ -515,7 +589,8 @@
 			}
 			%>
 		} else {
-			throw new RuntimeException("Job failed: " + job_<%=cid%>.getStatus().getError());
+			List<com.google.cloud.bigquery.BigQueryError> errorList = job_<%=cid%>.getStatus().getExecutionErrors();
+			throw new RuntimeException("Job failed: " + errorList.get(errorList.size() - 1));
 		}
 		
 		/* ----END-CREATING-JOB (Cloud API)---- */