rockstarETL: Dynamic SQL Job Type
It’s dynamic SQL!
August 2019
BigQuery: the promised land
BigQuery is the goal. The promised land. It is a marvel of engineering and features. Performance and usability are truly impressive. Just think, being able to run cloud data warehousing, ad hoc exploratory analysis AND machine learning! All this from one platform! Incredible. And then Google goes and throws in TensorFlow as well! Mind = blown! š …
Loading Data into BigQuery
How do I get my data into BigQuery? Let’s compare the options for loading data in BigQuery. Factors we’ll use are: Price Features Complexity and Risks If you look at Google’s Business IntelligenceĀ guide: We’ll compare the first four options above and then we’ll take a look at the Partner options. Cloud Dataflow and Cloud Dataprep …
rockstarETL: In a Nutshell
rockstarETL is a one-stop, codeless ETL/ELT orchestration platform. Pipelines -> Steps -> Jobs rockstarETL enables you to define pipelines. A pipeline is a sequence of steps executed in order from Step 1 through to the last step specified. Each step can have multiple jobs. These will be executed in parallel. Below, in Step 1, there are …
rockstarETL: Paths to Cloud Storage files
rockstarETL often asks you to specify a path when configuring your jobs. Eg: This is a text or json file you need to create. Inside should be either the SQL query statement. You then save this file in your Cloud Storage Bucket and tell rockstarETL where to find it. rockstarETL then executes the SQL statement …
rockstarETL: Load Job Type
The Load Job type let’s you load multiple files from your Cloud Storage bucket into separate BigQuery tables in one step! So you could load thirty to fifty BigQuery tables from the same number of different source folders. In addition, you could have thousands of files in each of these thirty to fifty sub-folders. The documentation explains …
rockstarETL: ConditionalSQL Job Type
This job is a BigQuery SQL statement that returns a boolean ie: “true” or “false”. The step will only complete once the result of the statement evaluates to “true”. You would use this anytime you need the step to only complete when some condition is met. Effectively you are able to pause or delay pipelines …
Limitations with Cloud Storage Transfer
While Cloud Storage Transfer does offer support for loading data on a schedule, there are some important limitations. Let’s take a look: rockstarETL does not share these limitations! Waiting an hour before being able to use your data may be a deal-breaker! By contrast, rockstarETL will detect your source files immediately: As soon as they are copied to …
Parametized queries
Limitations with BigQuery and how rockstarETL overcomes them! BigQuery does come with basic support for parametized queries. However, there are some severe limitations: Fortunately, rockstarETL overcomes these! rockstarETL’s dynamic SQL job type allows you to dynamically generate your queries without this limitation! Any part of the query can be parametized!