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:
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:
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.SUPPLIER T4 ON
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.PART T5 ON
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:
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 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.