How to import data about transactions, email campaigns, and custom events to Google BigQuery

This article describes the ways you can use to import data from your CRM/ERP systems, mailing services, or other online or offline sources to Google BigQuery.

Data import

To import data to Google BigQuery, use one of the following methods:

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

Consider the encoding and delimitersGoogle BigQuery supports the encodings UTF-8 (by default) and ISO-8859-1. The supported delimiters are comma (by default) and tab.
More details in Google documentation.

The recommended table schema

For the correct data importing and its further analysis, make sure you follow the recommended structure of Google BigQuery table. Refer to our documentation on schema definition depending on your data type:

Uploading data via Google BigQuery web interface (using transaction data as an example)

We recommend uploading data via the BigQuery web UI. To do that, make up a CSV table with your data and upload it following the instructions below.

  1. Open Google BigQuery web interface
  2. Create a new dataset in your Google BigQuery project. Select the project in the Resources section, then click Create dataset in the window to the right:Import_transactions_1.png
  3. Specify the Dataset ID and Data location. Note: the data location must be the same as the location of all the other data you want to blend in OWOX BI.
    Then, click Create dataset:Import_transactions_2.png
  4. Create a new table: Click the 'plus' icon on the right to the dataset name:Import_transactions_3.png
  5. Select Create table from: Upload, then specify the file location and format (CSV), then type in the name for the new table:Import_transactions_4.png
  6. Make up the data schema in the Schema section. You can add fields one by one by clicking Add Field and following this data schema, or simply click Edit as Text and paste the full schema in the JSON format (copy it from the snippet right after the screenshot below):Import_transactions_5.png

Copy the schema in JSON here:

7. Set Advanced options. Set Header rows to skip as so the data would be imported with no errors. You can also specify the allowed amount of errors to avoid errors when the table is created:Import_transactions_6.png

After clicking Create Table, the table will be uploaded to BigQuery:Import_transactions_7.png

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

0 Comments

Please sign in to leave a comment.