Add dataset parameters into column to use them in BigQuery later with DataPrep

Question

I am importing several files from Google Cloud Storage (GCS) through Google DataPrep and store the results in tables of Google BigQuery. The structure on GCS looks something like this:

//source/user/me/datasets/{month}/2017-01-31-file.csv
//source/user/me/datasets/{month}/2017-02-28-file.csv
//source/user/me/datasets/{month}/2017-03-31-file.csv

We can create a dataset with parameters as outlined on this page. This all works fine and I have been able to import it properly.

However, in this BigQuery table (output), I have no means of extracting only rows with for instance a parameter month in it.

How could I therefore add these Dataset Parameters (here: {month}) into my BigQuery table using DataPrep?

You should try to ask this question on the Trifacta forum (which is the actual solution running under the Google Dataprep naming) : community.trifacta.com/s/topic/0TO16000000g8CSGAY/… — Xavier Canton
The problem is that you are missing the filename in the resulting dataset? — Felipe Hoffa
Filename and the Date of the folder and another dynamic Subfolder. I store the files per datatype and day (yyyymmdd). I would like this to be reflected in the tables, as I am using Append to Table, such that I can filter out the daily file efficiently — JohnAndrews

JSDBroughton JSDBroughton · Accepted Answer · 2019-02-22T15:01:48

There is currently no access to data source location or parameter match values within the flow. Only the data in the dataset is available to you. (except SOURCEROWNUMBER())

Partial Solution

One method I have been using to mimic parameter insertion into the eventual table is to have multiple dataset imports by parameter and then union these before running your transformations into a final table.

For each known parameter search dataset, have a recipe that fills a column with that parameter per dataset and then union the results of each of these.

Obviously, this is only so scalable i.e. it works if you know the set of parameter values that will match. once you get to the granularity of time-stamp in the source file there is no way this is feasible.

In this example just the year value is the filtered parameter.

Longer Solution (An aside)

The alternative to this I eventually skated to was to define dataflow jobs using Dataprep, use these as dataflow templates and then run an orchestration function that ran the dataflow job (not dataprep) and amended the parameters for input AND output via the API. Then there was a transformation BigQuery Job that did the roundup append function.

Worth it if the flow is pretty settled, but not for adhoc; all depends on your scale.

Add dataset parameters into column to use them in BigQuery later with DataPrep

2 Answers