Return to Answer

added 1544 characters in body

Source Link

edited Apr 15, 2020 at 9:43

Your default location can be set in the .bigqueryrc file. The schema's location can be set using the bq command, like this:

bq --location=location load \

--source_format=format \
project_id:dataset.table_name \
path_to_source \
schema

Where:

location is the name of your location. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.

format is NEWLINE_DELIMITED_JSON or CSV.

project_id:dataset.table_name is your project ID.

project_id:dataset.table_name is the dataset that contains the table into which you're loading data.

project_id:dataset.table_name is the name of the table into which you're loading data.

path_to_source is the location of the CSV or JSON data file on your local machine or in Cloud Storage.

schema is the inline schema definition.

Example:

Enter the following command to load data from a local CSV file named myfile.csv into mydataset.mytable in your default project. The schema is manually specified inline.

bq load \

--source_format=CSV \
mydataset.mytable \
./myfile.csv \
qtr:STRING,sales:FLOAT,year:STRING

More on locations here.

Both your encoding and locale are suspect based on the details you have provided.

Your default location can be set in the .bigqueryrc file. The schema's location can be set using the bq command, like this:

bq --location=location load \

--source_format=format \
project_id:dataset.table_name \
path_to_source \
schema

Where:

location is the name of your location. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.

format is NEWLINE_DELIMITED_JSON or CSV.

project_id:dataset.table_name is your project ID.

project_id:dataset.table_name is the dataset that contains the table into which you're loading data.

project_id:dataset.table_name is the name of the table into which you're loading data.

path_to_source is the location of the CSV or JSON data file on your local machine or in Cloud Storage.

schema is the inline schema definition.

Example:

Enter the following command to load data from a local CSV file named myfile.csv into mydataset.mytable in your default project. The schema is manually specified inline.

bq load \

--source_format=CSV \
mydataset.mytable \
./myfile.csv \
qtr:STRING,sales:FLOAT,year:STRING

More on locations here.

Both your encoding and locale are suspect based on the details you have provided.

Source Link

created Apr 15, 2020 at 9:07

Mast

Based on my experience with how finicky CSV can be, I strongly suspect an encoding problem. A lot of CSV readers simply throw-away the data if they encounter illegal characters or when they can't figure it out. I don't know what BigQuery does in that regard, but considering their help on loading CSV files it's worth a shot to make sure your data is clean and according to how BigQuery wants it before attempting anything else.

Limitations

When you load CSV data from Cloud Storage into BigQuery, note the following:

CSV files do not support nested or repeated data.

If you use gzip compression BigQuery cannot read the data in parallel. Loading compressed CSV data into BigQuery is slower than loading uncompressed data.

You cannot include both compressed and uncompressed files in the same load job.

When you load CSV or JSON data, values in DATE columns must use the dash (-) separator and the date must be in the following format: YYYY-MM-DD (year-month-day).

When you load JSON or CSV data, values in TIMESTAMP columns must use a dash (-) separator for the date portion of the timestamp, and the date must be in the following format: YYYY-MM-DD (year-month-day). The hh:mm:ss (hour-minute-second) portion of the timestamp must use a colon (:) separator.

CSV encoding

BigQuery expects CSV data to be UTF-8 encoded. If you have CSV files with data encoded in ISO-8859-1 (also known as Latin-1) format, you should explicitly specify the encoding when you load your data so it can be converted to UTF-8.

Delimiters in CSV files can be any ISO-8859-1 single-byte character. To use a character in the range 128-255, you must encode the character as UTF-8. BigQuery converts the string to ISO-8859-1 encoding and uses the first byte of the encoded string to split the data in its raw, binary state.

BigQuery expects UTF-8, you provide CP-1252. CP-1252 should, in my opinion, have died out by now. So either it only looks like such or you have a program that doesn't care much about following recent standards (those exist, I use one of them every day).

Also, make sure your localisation is set correctly. Some CSVs are separated by ; instead of , and some locales consider , a thousands separator while others see it as decimal separator. So please verify whether the locale of your CSV is the same as the locale of your schema.

Both your encoding and locale are suspect based on the details you have provided.