Create and run a workflow in Dataform

Create a Dataform repository

1. In the Google Cloud console, go to the Dataformpage.

   Go to Dataform

2. Click add Create repository.

3. On the Create repositorypage, do the following:

   1. In the Repository IDfield, enter quickstart-repository .

   2. In the Regionlist, select europe-west4 .

   3. Click Create.

Create and initialize a Dataform development workspace

1. In the Google Cloud console, go to the Dataformpage.

   Go to Dataform

2. Click quickstart-repository .

3. Click add Create development workspace.

4. In the Create development workspacewindow, do the following:

   1. In the Workspace IDfield, enter quickstart-workspace .

   2. Click Create.

   The development workspace page appears.

5. Click Initialize workspace.

Create a view

In the following sections, you define a view that you will later use as a data source for a table.

Create a SQLX file for defining a view

1. In the Filespane, next to definitions/ , click the more_vert Moremenu.

2. Click Create file.

3. In the Create new filepane, do the following:

   1. In the Add a file pathfield, enter definitions/quickstart-source.sqlx .

   2. Click Create file.

Define a view

1. In the Filespane, expand the definitions folder.

2. Click definitions/quickstart-source.sqlx .

3. In the file, enter the following code snippet:

     config 
     
    { 
     
    type 
    : 
     
    "view" 
    } 
    SELECT 
     
    "apples" 
     
    AS 
     
    fruit 
    , 
     
    2 
     
    AS 
     
    count 
    UNION 
     
    ALL 
    SELECT 
     
    "oranges" 
     
    AS 
     
    fruit 
    , 
     
    5 
     
    AS 
     
    count 
    UNION 
     
    ALL 
    SELECT 
     
    "pears" 
     
    AS 
     
    fruit 
    , 
     
    1 
     
    AS 
     
    count 
    UNION 
     
    ALL 
    SELECT 
     
    "bananas" 
     
    AS 
     
    fruit 
    , 
     
    0 
     
    AS 
     
    count 
    
   

4. Click Format.

Create a table

In the following sections, you define the table type in a SQLX file, and then write a SELECT statement to define the table structure within the same file.

Create a SQLX file for table definition

1. In the Filespane, next to definitions/ , click the more_vert Moremenu, and then select Create file.

2. In the Add a file pathfield, enter definitions/quickstart-table.sqlx .

3. Click Create file.

Define the table type, structure and dependencies

1. In the Filespane, expand the definitions/ directory.

2. Select quickstart-table.sqlx , and then enter the following table type and SELECT statement:

     config 
     
    { 
     
    type 
    : 
     
    "table" 
    } 
    SELECT 
     
    fruit 
    , 
     
    SUM 
    ( 
    count 
    ) 
     
    as 
     
    count 
    FROM 
     
    ${ 
    ref 
    ( 
    "quickstart-source" 
    ) 
    } 
    GROUP 
     
    BY 
     
    1 
    
   

3. Click Format.

After defining the table type, Dataform throws a query validation error because quickstart-source does not exist in BigQuery yet. This error is resolved when you run the workflow later in this tutorial.

Run the workflow in BigQuery

1. In the Google Cloud console, go to the Dataformpage.

   Go to Dataform

2. On the quickstart-workspace page, click Start execution.

3. Click All actions.

4. Click Start execution.

5. In the dialog that opens, click Allowto give permission to BigQuery Pipelinesto access your Google Account.

   Dataform uses the default repository settings to create the contents of your workflow in a BigQuery dataset called dataform .

View execution logs in Dataform

1. On the quickstart-repository page, click Workflow Execution Logs.

2. To view details of your execution, click the latest execution.