Shantanu's Blog

Here is a script that will dump the data from mysql and upload it to google cloud. It will then import the data from cloud to Google BigQuery database.

#!/bin/sh
yester_day=`date '+%Y%m%d' --date="1 day ago"`
schema_name="miniLogArchives"
tbl_name="r_mini_raw_$yester_day"
raw_location="/mnt/$yester_day"
field_term="^"

file_prefix='may3'

# split 4 GB
split_bytes="4000000000"
# google bucket name
bucket_name='log_data'

mkdir $raw_location
cd $raw_location
time mysqldump $schema_name $tbl_name --tab=$raw_location --fields-terminated-by="$field_term"

# make sure input file is UTF-8 file

file $tbl_name.txt

# split files with prefix 
time split -C 4000000000 $tbl_name.txt $file_prefix

for file in `ls $file_prefix*`
do

# big query does not seem to like double quotes

time sed -i 's/\"//' $file
time gzip $file

# copy to google cloud and then import data to big query

time gsutil cp $file.gz gs://$bucket_name/
time bq --nosync load -F '^' --max_bad_record=30000 mycompany.raw_data gs://$bucket_name/$file.gz ip:string,cb:string,country:string,telco_name:string, ...

done

exit

# make sure that mysql has write permission to raw_location path /mnt/
# make sure the 2 utilities are installed from ...
# https://developers.google.com/storage/docs/gsutil_install#install
# http://code.google.com/p/google-bigquery-tools/downloads/list

Here is another shell script that will copy the current MySQL table to big-query.

time mysql -e"drop table if exists test.google_query"

time mysql vserv -e"create table test.google_query engine=MyISAM
SELECT s.zone_id AS zone_id, ...
WHERE ncyid = 3 AND s.date_time>='2012-07-01 00:00:00' AND s.date_time<='2012-07-02 23:59:59' "

time mysqldump test google_query --tab=/mnt/
time gzip /mnt/google_query.txt

time bq --nosync load  -F '\t' --max_bad_record=30000 --job_id summary_2012_07_09_to_2012_07_02  company.summary02 /mnt/google_query.txt.gz zone_id:string,requests:integer,total_revenue:float,...

time bq query "SELECT FROM company.summary02 WHERE date_time>='2012-07-01 00:00:00' AND date_time<='2012-07-02 23:59:59' GROUP BY continent, country"

// this tutorial shows how to integrate big-query results into google spreadsheet.

https://developers.google.com/apps-script/articles/bigquery_tutorial

// gsutil can be used with both, google cloud as well as aws s3 provided you have added the aws_key and aws_secret_key to ~/.boto file:
gsutil -m cp -R s3://from_aws/ gs://daily_log/

// gsutil supports multi-thread option while s3cmd supports sync option as shown below:
s3cmd sync local_dir s3://from_aws/

// create JSON schema from MySQL save it as summary.json
select CONCAT('{"name": "', COLUMN_NAME, '","type":"', IF(DATA_TYPE like "%int%", "INTEGER",IF(DATA_TYPE="decimal","FLOAT","STRING")) , '"},') as json from information_schema.columns where TABLE_SCHEMA = 'test' AND TABLE_NAME = 'summary'

mysqldump test summary --tab=.

time bq load --nosync -F '\t' --max_bad_record=30000 company.ox_banners ./ox_banners.txt ./ox_banners.json

_____

Here is the script that will copy the MySQL table to Big Query table

#!/bin/sh
BIG_QUERY_DB='company'
# change the big query db variables and then
# run the script with mysql DB and Table name for e.g.
# sh -xv query.sh asgs1a_vol_e6d65888 ox_data_archive_r_20120727

if [ $# -eq 0 ]
  then
    echo "DB and table name required"
exit 1
fi

TABLE_SCHEMA=$1
TABLE_NAME=$2

cat > json_query.txt << heredoc
select CONCAT('{"name": "', COLUMN_NAME, '","type":"', IF(DATA_TYPE like "%int%", "INTEGER",IF(DATA_TYPE="decimal","FLOAT","STRING")) , '"},') as json from information_schema.columns where TABLE_SCHEMA = '$TABLE_SCHEMA' AND TABLE_NAME = '$TABLE_NAME';
heredoc

echo '[' >  $TABLE_NAME.json
mysql -Bs < json_query.txt | sed '$s/,$//' >> $TABLE_NAME.json
echo ']' >>  $TABLE_NAME.json


mysqldump $TABLE_SCHEMA $TABLE_NAME --tab=.
time sed 's/\"//' $TABLE_NAME.txt | gzip -c > $TABLE_NAME.txt.gz

mytime=`date '+%y%m%d%H%M'`
time bq load --nosync -F '\t' --job_id="$TABLE_NAME$mytime" --max_bad_record=30000 $BIG_QUERY_DB.$TABLE_NAME $TABLE_NAME.txt.gz $TABLE_NAME.json

# download the script
wget http://mysqldump.googlecode.com/files/big_query.sh

# if required use dos2unix new_query.sh command to make it in linux format
# now dump and transfer the data from table mytable of company database
sh -xv big_query.sh company mytable

# If you need to process the text files, use the following scirpt
wget http://mysqldump.googlecode.com/files/final_big_query.sh
 _____

The following script will remove all the data from google cloud.
Use it with utmost care!

#!/bin/sh
for mybucket in `gsutil ls`
do
gsutil rm -R $mybucket > /dev/null
gsutil rb $mybucket
done

Script to remove all tables from big query

#!/bin/sh
# can add -r -f to rm command
# if you want to forcefully remove all tables
# datasetId from the command "bq ls"
datasetId=company

for mytable in `bq ls $datasetId`
do
bq rm $datasetId.$mytable
done

#You were warned