Export RedCall Data to BigQuery

[dev-mansonthomas]

• the export should not include first name, last name, mobile, email
• we'll need to see how to build the queries that perform the exports to ease the analysis
• the export could be done 4 times a day

BigQuery would be then queried by Google Data Studio or Google Spreadsheet to do some analysis on the data.

(linked to #360, that must be splitted ;))

[dev-mansonthomas]

https://stackoverflow.com/questions/41774233/best-practice-to-migrate-data-from-mysql-to-bigquery

Exporting data from MySQL
First, export the data from MySQL this way:

SELECT * INTO OUTFILE 'filename.csv' CHARACTER SET 'utf8'
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY ''
FROM table
This is in reality a tsv file (tab separated values), but you can import them as csv thought.

Import into Big Query
This way you should be able to import it into big query with the following parameters:

bq load --field_delimiter="\t" --null_marker="\N" --quote=""
PROJECT:DATASET.tableName gs://bucket/data.csv.gz table_schema.json
Notes
If any field in you MySQL database contains a tab character (\t), it will break your columns. To prevent that you can add the SQL function REPLACE(, '\t', ' ') on the columns and it will convert from tabs to spaces.

If you set the table schema in big query's web interface you won't need to specify it every time you load a CSV.

I hope this works for you.

[ninsuo]

INTO OUTFILE will create a file on the local sql instance, not on the client side, so it won't work as is.

[dev-mansonthomas]

Maybe use something like this :
https://vauly.com/symfony2-export-csv