I want to know the best way (best performance and reaction time) to insert all rows of an SQL table into a Google BigQuery table. Actually, I am looping over a query result and insert the rows one by one into the BigQuery table.
The query GetShippingList
returns all TShipping_ID
of the table Shipping
which has only one column:TShipping_ID
. So, the SQL table has exactly the same schema of the BigQuery
table.
<cfloop query="GetShippingList">
<cfset DateTime=mid("#now()#",6,19)>
<cfset TShipping_ID=TShipping_ID>
<!--- instancier un objet de type (class) TableRow --->
<cfobject action="create" type="java" class="com.google.api.services.bigquery.model.TableRow" name="row">
<cfset row.set("DateTime",DateTime)>
<cfset row.set("TShipping_ID", TShipping_ID)>
<!--- instancier un objet de type (class) TableDataInsertAllRequest.Rows --->
<cfobject action="create" type="java" class="com.google.api.services.bigquery.model.TableDataInsertAllRequest$Rows" name="rows">
<cfdump var="#rows#">
<!--- instancier un objet de type (class) TableDataInsertAllRequest --->
<cfobject action="create" type="java" class="java.lang.System" name="sys">
<cfdump var="#sys#">
<cfset rows.setInsertId(sys.currentTimeMillis())>
<cfset rows.setJson(row)>
<!--- instancier un objet de type (class) ArrayList --->
<cfobject action="create" type="java" class="java.util.ArrayList" name="rowList">
<cfdump var="#rowList#">
<cfset rowList.add(rows)>
<!--- instancier un objet de type (class) TableDataInsertAllRequest --->
<cfobject action="create" type="java" class="com.google.api.services.bigquery.model.TableDataInsertAllRequest" name="rqst">
<cfdump var="#rqst#">
<cfset content=rqst.setRows(rowList)>
<!--- instancier un objet de type (class) TableDataInsertAllResponse --->
<cfobject action="create" type="java" class="com.google.api.services.bigquery.model.TableDataInsertAllResponse" name="rsp">
<cfdump var="#rsp#">
<cfset response = bq.tabledata().insertAll("vigicolis", "wi_vigicolis", "TShipping", content).execute()>
</cfloop>