How to upload transaction data into Google BigQuery?

You can use libraries and applications to automate data transfer from CRM to Google BigQuery:

  1. Upload files in CSV or JSON format using BigQuery UI or a command line
  2. Google Sheets data upload using OWOX BI BigQuery Reports add-on
  3. ETL applications
  4. ODBC-driver from CDATA
  5. SDK for .NET, Java, PHP, Python

 

Let's walk through importing data using BigQuery UI:

  1. GO TO THE BIGQUERY WEB UI

  2. In the navigation panel, hover on a dataset, click the down arrow icon down arrow icon image, and click Create new table. The process for loading data is the same as the process for creating an empty table.

  3. On the Create Table page, in the Source Data section:

    • For Location, select File upload, click Choose file, browse to the file, and click Open. Note that wildcards and comma-separated lists are not supported for local files.
    • For File format, select (CSV)JSON (newline delimited)AvroParquet, or ORC.
  4. On the Create Table page, in the Destination Table section:
    • For Table name, choose the appropriate dataset, and in the table name field, enter the name of the table you're creating in BigQuery.
    • Verify that Table type is set to Native table.
  5. In the Schema section, enter the schema definition.

    • For CSV and JSON files, you can check the Auto-detect option to enable schema auto-detect. Schema information is retrieved from Avro, Parquet, and ORC files using the source data.

      auto detect link

    • You can also enter schema information manually by:

      • Clicking Edit as text and entering the table schema as a JSON array:

        Add schema as JSON array

      • Using Add Field to manually input the schema:

        Add schema using add fields

  6. Select applicable items in the Options section and then click Create Table. For information on the available options, see CSV options and JSON options.

 

Consider character encodings and delimitersCurrently your data must be UTF-8 (by default) or ISO-8859-1 encoded. More details in Google BigQuery documentation
As a delimiter, Google BigQuery recognizes a comma or a tab separator. More details in Google BigQuery documentation

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.