2
\$\begingroup\$

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>
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.