Nsryjdtyk

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>