How do I change the location of Google BigQuery

Question

So, I'm trying to use Google BigQuery for the first time for a project of mine, and I'm a bit confused. The documentation isn't helping much, and it looks like all the Google employees are gone thanks to the current epidemic, judging by the blogpost by Google.

I've got several .csv files containing tables of unlabeled numerical data uploaded to Google Cloud storage, in their Australian servers. What I want to do is to use BigQuery to perform k-means clustering and analysis of correlations between variables on them. However, I'm having issues trying to get the data transferred into BigQuery.

I tried using the "Data Labeling" tab to create a data set like their documentation suggests, but it only reads one CSV at a time (I want to combine all the CSVs into one dataset), and none of the data types seem to fit at first glance, with the only one that sort of matches being "text". However, once it finishes its process, it looks like the resulting dataset is empty, when the original CSVs have hundreds of rows and dozens of columns. I've also tried using the "Tables" tab to create a data set, but it is giving me an error message about needing to put my models in the same region as my data, and I can't see how to fix this; the only options it seems to be giving me seem to be "Global" and "European Union", so I'm not certain how to set it to "Australia".

Does anyone have any ideas about how to fix this?

An anonymized version of the data sort of looks like this (but the actual CSV is much larger, with many more rows and columns):

Item_ID,From_Position,To_Position,Data_One,Data_Two,Data_Three AA22DDA002,103,104.0,182.35643356,8.12345678,-2.32453256,0.03234213 AA22DDA002,104.0,105.0,483.23424678,3.34627319,-4.37600000

Answer 1

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.

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.