I have a huge dataset in bigquery with 50million rows and 57 columns. I want to do a lot of filtering/transformation/cleaning not using sql. I tried using dask/panda/python to load data in a dask dataframe in my local mac, do the transformation stuff then push data back to bigquery so other BUs can use it. Pushing data back to bigquery takes more than 3 hours. Is there any other way or maybe google cloud service that I can leverage?
0
votes
When mentioning thing like "Pushing data back to bigquery takes long." Be specific, how much data in MB, how much time, the speed of your Internet connection, etc. Otherwise we have no idea what is "long time".
- John Hanley
Migrate your app to PYTHON ON GOOGLE CLOUD PLATFORM, write intermediate file onto Google Cloud Storage and instruct BigQuery to load from GCS ?
- Yun Zhang
Why don't you want to use SQL? Don't forget, you can also inject JavaScript into your transformations too. The benefit of staying in BigQuery is that you bring your code to the data, not the data to the code. This has huge advantages i.e. scale and simplicity.
- Graham Polley
@GrahamPolley some of our usecases are complex and we have to create a lot of temp tables for only one metric.
- MT467
That's ok. You can do this in BigQuery using a orchestration tool e.g. Airflow/Composer. We do it all the time. As soon as you jump out of BigQuery you're going to take a big performance hit.
- Graham Polley
1 Answers
1
votes
If you have a large amount of data within BigQuery and wish to perform transformation upon it, one possible solution would be to use the GCP based capability called Dataflow. Dataflow is Googles managed service based upon Apache Beam. Using this technology one can write a pipeline with both BigQuery as a source and as a sink. Dataflow is specifically designed for extremely high volume data processing and can parallelize the work automatically. In addition, since it all runs within GCP, there is no meaningful latency in reading or writing the data as might be found if you were transferring it over the Internet. Dataflow allows a programmer to write transformations in either Java or Python.
Depending on your transformation, a higher level (but similar story) might be the use of Google's Dataprep service. Dataprep provides a high level (business level) mechanism to transform data without any programming required. Using Dataprep, one describes the transform at a much higher level which eventually automatically builds and runs a Datalow job on your behalf.
