title: Best Practice to migrate data from MySQL to BigQuery
body: <p>I tried several csv-formats (different escape characters, quotes and other settings) to export data from MySQL and to import it into BigQuery, but I was not able to find a solution that works in every case.</p>
  <p><a href="https://cloud.google.com/sql/docs/mysql/import-export/" rel="nofollow noreferrer">Google SQL requires</a> the following Code for importing/exporting from/to MySQL. Although, Cloud SQL is not BigQuery, it is a good starting point:</p>
  <pre><code>SELECT * INTO OUTFILE 'filename.csv' CHARACTER SET 'utf8' 
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\&quot;' ESCAPED BY '' FROM table
  </code></pre>
  <p>At the moment I use the following command to import a compressed csv into BigQuery:</p>
  <pre><code>bq --nosync load -F &quot;,&quot; --null_marker &quot;NULL&quot; --format=csv PROJECT:DATASET.tableName gs://bucket/data.csv.gz table_schema.json
  </code></pre>
  <p>On one hand the bq-command does not allow to set the escape character (<code>&quot;</code> is escaped by another <code>&quot;</code>, which seems to be a well defined <a href="https://www.rfc-editor.org/rfc/rfc4180" rel="nofollow noreferrer">CSV</a>-format). On the other hand <code>\&quot;</code> as escape character for MySQL-export would lead to <code>&quot;N</code> as Null-value, which does not work too:</p>
  <pre><code>CSV table references column position 34, but line starting at position:0 contains only 34 columns. (error code: invalid)
  </code></pre>
  <p>So my question is: How to write a (table-independent) export command for MySQL in SQL, such that the generated file can be loaded into BigQuery. Which escape character should be used and how to handle/set null values?</p>