Dev4Snow

Go back to Contents – Go back to previous page

Query Builder Tab

In this tab you can graphically create a query and run it. It is particularly useful when you need to join multiple tables and you don’t want to write all the code manually.

Follow these steps to create a new query:

  • Tables and views can be dragged and dropped from the Tree into the diagram.
  • Drag an arrow from one object/column to another object/column to define the columns that are joining and the direction of Joins.
  • When double-clicking on a column you select it for display. Selected columns display in red in the diagram.
  • From the context menu you can filter on a column and it will display with a filter icon in the diagram.
  • From the context menu you can add an aggregate function to a column and it will display with a sigma summation symbol (Σ) in the diagram.
  • From the context menu of the first object in the diagram you can add COUNT(*).
  • From the context menu of any object except the first object you can set up a table for Outer Join.

 

In the example above the tables were dragged and dropped from the Tree into the diagram, then connected by just dragging arrows from one object to the other, then columns in red –selected columns- were selected by double-clicking on them and a filter was added by right-clicking on the Customer table. Finally the last table (“PARTSUPP”) was marked as optional –records may not exist for it- and the query was run –results were shown in the Results window.

By clicking the SQL icon you can check the SQL code produced, which in this case is this one below:

SELECT

      T1.C_NAME,
      T1.C_PHONE,
      T2.O_ORDERKEY,
      T2.O_ORDERDATE,
      T3.L_PARTKEY,
      T3.L_SUPPKEY,
      T3.L_LINENUMBER,
      T3.L_QUANTITY,
      T4.S_NAME,
      T5.P_NAME,
      T6.PS_SUPPLYCOST

FROM
      SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.CUSTOMER T1

        INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.ORDERS T2 ON
          T1.C_CUSTKEY = T2.O_ORDERKEY

          INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM T3 ON
            T2.O_ORDERKEY = T3.L_ORDERKEY
 

            INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.SUPPLIER T4 ON
              T3.L_SUPPKEY = T4.S_SUPPKEY
 

              INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.PART T5 ON
                T3.L_PARTKEY = T5.P_PARTKEY

                LEFT OUTER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.PARTSUPP T6 ON
                  T5.P_PARTKEY = T6.PS_PARTKEY AND
                  T4.S_SUPPKEY = T6.PS_SUPPKEY

WHERE
      T1.C_NAME in ('Customer#006786883','Customer#006786915')
;

 

Icons & actions:

Clear / Create a new query: Clears the diagram to start a new query.

Open an existing query from Query Builder: Opens a query previously saved by the Query Builder.

Save the Query Builder query: Saves the diagram in a proprietary format –not SQL script. If you want to save the SQL script you can either export copy it manually from the SQL button or export it to a SQL Code Window.

Generate PDF for Diagram: Generates a PDF document for the diagram.

Copy diagram to Clipboard: Copies the diagram to Clipboard so it can be pasted in another application –like Word.

Show generated SQL query: Shows the SQL query generated from the diagram.

Move SQL query to SQL Code Window: Creates a new SQL Code Window and moves the SQL query generated from the diagram to it.

Execute generated query: Executes the SQL query generated based on the diagram. Shortcut: Ctrl+Enter or F5.

Stop running query: Sends an instruction to Snowflake to stop the currently running query.

Change behavior for auto-link feature: When adding a new table/view to the diagram the auto-link feature will attempt to match by name the object columns to the columns of the previous object that was added to the diagram. These are the possible behaviors:

  • Auto-link: All the columns from the last object are compared to all the columns in the previous object. If the names match a new arrow (matching condition) will be created starting from the previous object column to the last object column.
  • Auto-link PK: Only the Primary Key columns from any of the two objects (previous or last) are compared looking for a matching on the other object. The Primary Key column needs to exist in only one of the tables to be considered for matching. If a matching exists the result is an arrow starting from the previous object column to the last object column –where the column is part of a Primary Key in at least one of the objects.
  • Manual link: Auto-link is disabled. You need to link the objects manually by dragging arrows from one object to the other.

NOTE: This feature sometimes produces an excessive number of arrows for columns that are not related. You can use CTRL+LEFT_MOUSE_KEY to select all the unnecessary arrows and hit <DEL> to remove them all at once.

Zoom-In & Zoom-Out for Diagram: Zooms in and zooms out the diagram.

Refresh definition for Diagram objects: Refreshes the definition –columns and data types- for objects in the diagram. Particularly useful if you saved the diagram in a previous day and you want to reuse the query generated by it, in this case it is convenient to refresh the definition in case some objects changed. The Query Builder will refresh the objects while keeping your selections/filters –unless some column is no longer available, which is automatically resolved by removing the selection/conditions for the column.

Show or hide Quick help: Shows or hides the quick help window.

Show or hide Results window: Shows or hides the panel where the query results are displayed. Shortcut: Ctrl+R.

 

Context Menus:

Context menus activate when right-clicking on a diagram object.

Depending on which object you right-clicked, or even if you clicked or not on a column, different options will show up. For example in the case below the Customer table was right-clicked over column C_ACCTBAL. Since Customer table was the first table in the set –the starting object- the option “Add or remove Count(*)” showed up. This option is only available for the starting object of a full diagram.

Also since the column that was clicked was numeric, it allowed for aggregation so the option “Aggregate on column C_ACCTBAL” showed up.

In this second case below the table ORDERS was right-clicked, and since it’s not the starting object the option “Add or remove Count(*)” is no longer showing up. A couple of new options showed for this table at the bottom of the context menu: “Rows must exist (Inner Join)” and “Rows may not exist (Outer Join)”. These two options are available for all the tables except for the starting object. It produces an Inner or Outer Join from table Customer to table Orders, but since Customer table is on the left side of the relationship it must always exist –that’s why these option do not show up for starting objects.

Filtering on column <column>: This option shows up when right-clicking on an object column. Use this option to add filters to that column.

Aggregate on column <column>: This option shows up only when right-clicking on a numeric column. Use this option to add an aggregate function on that column (sum, count, avg, min, max, etc). When adding an aggregate function in at least one column of any table, all of the other non-aggregate columns will be added to the GROUP BY clause.

Add or remove Count(*): This option shows up when clicking on the starting object. Since it adds an aggregate function it will cause all non-aggregate columns to be added to the GROUP BY clause.

Select all columns: Add all the columns from the right-clicked object to the SELECT clause.

Deselect all columns: Removes all the columns from the right-clicked object from the SELECT clause.

Data preview: Preview data that exists in this object. Useful to understand the data that is found in the object columns.

Invert all outgoing arrows: Inverts the direction of outgoing arrows. The direction of arrows defines the direction of Joins.

Delete all outgoing arrows: Delete all outgoing arrows.

Expand object height: When a table/view has more than 15 columns, only the first 15 columns for the object will display in the diagram. This option will show up when right-clicking the object, click it to all columns for the object.

Reset object height: When a table/view has more than 15 columns, only the first 15 columns for the object will display in the diagram. This option will show up when right-clicking the object, click it to all columns for the object.

Rows must exist (Inner Join): <default option>. This option shows up when the object is not a starting object. If selected the joins to this table happens using an Inner Join.

Rows may not exist (Outer Join): This option shows up when the object is not a starting object. If selected the joins to this table will happen using an Outer Join.

 

Filtering on column

When right-clicking on a column for filtering the following screen will open:

Change the Operator and Value to activate the filter. You can select the possible values from the list of Sample values. The list of Sample values can be configured to retrieve these values automatically, manually, or if asking every time you open the filtering.

 

Aggregate on column

When right-clicking on a numeric column to add aggregate function the following screen will open:

You can select an aggregate function from the list of functions, and also you can apply an optional filtering on the result –that will be used in the HAVING clause. The rest of the columns that don’t have an aggregate function will be used in the GROUP BY clause.