FAQ: BigQuery destination

Can I define the name of the not-existed table in the importer destination settings?

The short answer is Yes. 

You can define in Coupler.io importer settings the name of the table that does not exist and during the import, it will be created automatically.

How Coupler.io will understand what data types I am importing to BigQuery?

Coupler.io uses the BigQuery schema auto-detection mechanism for data importing to BigQuery. Here is an explanation of how it works from the Google Cloud BQ guide:

When auto-detection is enabled, BigQuery starts the inference process by selecting a random file in the data source and scanning up to 100 rows of data to use as a representative sample.
BigQuery then examines each field and attempts to assign a data type to that field based on the values in the sample.

Am I able to define table schema manually and force Coupler.io to load data in the needed type?

Unfortunately, right now Coupler.io does not have this possibility. Schema is defined automatically on each run of the importer.

How to name columns in the BigQuery destination table?

There are 2 important points:

  1. If you want to use a new table, you do need to create a table and define columns in BigQuery.
  2. Your data source needs to contain the first row with the names of the columns for the destination table.
BigQuery has specific rules for field names:
A column name must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_), and it must start with a letter or underscore. The maximum column name length is 300 characters. A column name cannot use any of the following prefixes:
 
_TABLE_
 _FILE_
 _PARTITION

How to force data importing as STRINGs?

  1. Do not create a table in the BigQuery.
  2. Prepare your file in a next way:
    - define 1st row with names of the columns (as you wanted to do in BQ, but applying BQ rules) + with 1 extra column Tech_ID - so we will avoid issues with default namings
    i.e. Tech_ID,Phone_1,Date,Text_Info,Comment

    - define 2nd row with INTEGER Tech_ID and other random string values (so schema auto-detection will define all your data as strings)  -
    i.e.  0,special row,special row,special row,special row

    - from 3d row insert your data as you want, but adding the first numeric value for Tech_ID -
    i.e.  1,0506664534,2019-04-01 3:00:00,Vancouver,Thanks!
  3. Setup importer using prepared file as a source and define the name of the not existed table in your dataset in BQ.
  4. Run the importer.

And here is the result of the data representation in the BigQuery after import:

  • table schema:
  • data imported:

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.