ingestion - La mejor práctica para migrar datos de MySQL a BigQuery

insert_rows bigquery

Question

Probé varios formatos csv (diferentes caracteres de escape, comillas y otras configuraciones) para exportar datos de MySQL e importarlos a BigQuery, pero no pude encontrar una solución que funcione en todos los casos.

Google SQL requiere el siguiente Código para importar / exportar desde / a MySQL. Aunque Cloud SQL no es BigQuery, es un buen punto de partida:

SELECT * INTO OUTFILE ''filename.csv'' CHARACTER SET ''utf8'' 
FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''/"'' ESCAPED BY '''' FROM table

Por el momento, utilizo el siguiente comando para importar un csv comprimido a BigQuery: bq --nosync load -F "," --null_marker "NULL" --format=csv PROJECT:DATASET.tableName gs://bucket/data.csv.gz table_schema.json

Por un lado, el comando bq no permite establecer el carácter de escape ( " se escapó por otro " , que parece ser un formato CSV bien definido). Por otro lado, /" como carácter de escape para MySQL-export conduciría a "N como valor nulo, que tampoco funciona:

CSV table references column position 34, but line starting at position:0 contains only 34 columns. (error code: invalid)

Entonces mi pregunta es: Cómo escribir un comando de exportación (independiente de la tabla) para MySQL en SQL, de modo que el archivo generado pueda cargarse en BigQuery. ¿Qué carácter de escape se debe usar y cómo manejar / establecer valores nulos?

Answer 1

He estado corriendo con el mismo problema, esta es mi solución:

Exportar datos de MySQL

Primero, exporte los datos de MySQL de esta manera:

SELECT * INTO OUTFILE ''filename.csv'' CHARACTER SET ''utf8'' 
FIELDS TERMINATED BY ''/t'' OPTIONALLY ENCLOSED BY '''' 
FROM table <yourtable>

Esto es en realidad un archivo tsv (valores separados por tabuladores), pero puede importarlos como csv thought.

Importar a Big Query

De esta manera, deberías poder importarlo a consulta grande con los siguientes parámetros:

bq load --field_delimiter="/t" --null_marker="/N" --quote="" /
PROJECT:DATASET.tableName gs://bucket/data.csv.gz table_schema.json

Notas

1. Si algún campo en su base de datos MySQL contiene un carácter de tabulación ( /t ), romperá sus columnas. Para evitar eso, puede agregar la función SQL REPLACE(<column>, ''/t'', '' '') en las columnas y convertirá de pestañas a espacios.

2. Si configura el esquema de la tabla en la interfaz web de la consulta grande, no tendrá que especificarlo cada vez que cargue un archivo CSV.

Espero que esto funcione para ti.

Answer 2

Puede usar una herramienta como mysql2xxxx para una flexibilidad máxima al exportar.

Con mysql2csv puede ejecutar una consulta arbitraria, y el proceso de salida aprovecha FasterCSV , que le dará más opciones que las existencias mysql.

Answer 3

Usar el siguiente comando SQL parece funcionar para mí, produciendo valores nulos con /N :

SELECT * INTO OUTFILE ''/tmp/foo.csv'' CHARACTER SET ''utf8''  
FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''/"'' ESCAPED BY "//" 
FROM table;

Con esto, deberías poder importar los datos usando --null_marker="/N" ¿Puedes probarlo y decirme si no funciona para ti?