How to preserve ASCII control characters when loading into BigQuery

Thilina 0
Viewed 37

I'm trying to import data into a BigQuery table from a CSV file using Bigquery python API. Since, it contains some ASCII control characters, loading job is getting failed with below error.

CSV file contains the ASCII 0 (NULL) character, you can't load the data into BigQuery.

bigquery documentation

It is observed that we can allow ascii control characters by setting --preserve_ascii_control_characters=true from bq command line (documentation). But the same functionality cannot be found via python API. Is there any work around to avoid this issue?

sample code:

import six

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("name", "STRING"),
        bigquery.SchemaField("post_abbr", "STRING"),
    ],
)

body = six.BytesIO(b"Washington,WA")
client.load_table_from_file(body, table_id, job_config=job_config).result()
previous_rows = client.get_table(table_id).num_rows
assert previous_rows > 0

job_config = bigquery.LoadJobConfig(
    write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE,
    source_format=bigquery.SourceFormat.CSV,
    skip_leading_rows=1,
)

uri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
load_job = client.load_table_from_uri(
    uri, table_id, job_config=job_config
)  # Make an API request.

load_job.result()  # Waits for the job to complete.

destination_table = client.get_table(table_id)
print("Loaded {} rows.".format(destination_table.num_rows))
ascii csv google-bigquery python
1 Answers

As mentioned by @Ricco D in the comments, preserve_Ascii_ControlCharacters is yet to be implemented for BQ client Libraries. You can follow this Feature request to get updates on when the feature is rolled out.

Meanwhile you can follow the following workarounds:

  • REST API and set JobLoadConfiguration preserveAsciiControlCharacters to true

  • bq command line using flag --preserve_ascii_control_characters=true

Sakshi Gatyan0
Related