Use case : A single query creates a table having 6000 columns and 93Million records.
Issue : This query mostly fails with resources exceed issue.
Try No 1 : As a part of optimization I break down the query to multiple subtasks to run in parallel(Using some python code and clustering column). Even after doing so the query takes 20-25 mins to finish.
Explaination : here first task create a query using CTAS CREATE OR REPLACE TABLE <tbl1> cluster by col1 AS SELECT * FROM <tbl2> WHERE col1 BETWEEN 1 and 2
Rest other tasks are INSERT INTO TABLE <tbl1> SELECT * FROM <tbl2> WHERE col1 BETWEEN 3 and 30 .
Note - The col1(clustering column) values keeps on changing for each task.
Try No 2 : I am seeing links around running queries asynchronously. Will it be helpful here in this case where I am already dividing the query in multiple subtasks?
Any help is appreciated for optimizing the run time.