Copying large BigQuery Tables to Google Cloud Storage and subsequent local download

0
votes

My goal is to locally save a BigQuery table to be able to perform some analyses. To save it locally, i tried to export it to Google Cloud Storage as a csv file. Alas the dataset is too big to move it as one file, thus it is splitted into many different files, looking like this:

exampledata.csv000000000000
exampledata.csv000000000001
...

Is there a way to put them back together again in the Google Cloud Storage? Maybe even change the format to csv?

My approach was to download it and try to change it manually. Clicking on it does not work, as it will save it as a BIN.file and is also very time consuming. Furthermore I do not know how to assemble them back together.

I also tried to get it via the gsutil command, and I was able to save them on my machine, but as zipped files. When unzipping with WinRar, it gives me exampledata.out files, which I do not know what to do with. Additionally I am clueless how to put them back together in one file..

How can I get the table to my computer, as one file, and as a csv?

The computer I am working with runs on Ubuntu, but I need to have the data on a Google Virtual Machine, using Windows Server 2012.

2
google-bigquerygoogle-cloud-storage
are the exampledata files formated as csv? - Daryl Wenman-Bateson
@DarylWenman-Bateson I unzipped them with 7zip. When opening with the notepad, it seems to have a csv.file structure. - Arthur Pennt
you most likely will get few good answers on your particular question. But please keep in mind - the motivation behind your question (which is locally save a BigQuery table to be able to perform some analyses) does not make much sense! you might want to rethink your approach of storing too big data in BigQuery but trying to analyse it locally. - Mikhail Berlyant
I agree with Mikhail. This doesn't make a lot of sense to me either. Why not just do your analysis in BigQuery!? Anyway, you can merge them in GCS using gsutil if that's really what you want to do: cloud.google.com/storage/docs/gsutil/commands/compose - Graham Polley
I will analyze the data with a different tool on the virtual machine and add the numerical results to the data set. This tool just works locally that is why i need to download it. Thank you for your help and thoughts! - Arthur Pennt

2 Answers

0
votes

try using the following to merge all files into one from the windows command prompt

copy *.cs* merged.csv

0
votes

Suggest you to save the file as .gzip file, then you can download it from Google Cloud easily as BIN file. If you get these splited files in bigquery as following: Export Table -> csv format, compression as GZIP, URI: file_name* Then you can combine them back by doing steps as below:

In windows:

  1. add .zip at the end all these files.

  2. use 7-zip to unzip the first .zip file, with name "...000000000000", then it will automatically detect all the rest .zip files. This is just like the normal way to unzip a splitted .zip file.

In Ubuntu:

I failed to unzip the file following the method I can find in internet. Will update the answer if I figure it out.