Once you enter to the SQL Models product you get immediately introduced to the Model Structure Section, where you can setting the necessary queries to transform the data.
Let's now introduce this structure section by all its elements:
1- Structure button.
You need to be in the structure section in order to start working on the model.
2- Query box.
Here you can set your query, here the transformation takes place.
SQL Model uses all the SQL Big query functions and the same syntax.
There is no limit of what SQL Model can do because it works on top of Google Big query and uses the same syntax as the Big query SQL uses.
Some tips on the syntax:
In order to select tables and columns you simply follow the logic of the query the tables and columns will be automatically suggested to you.
In every step of writing a query after any keyboard space typed, a suggestion window will open suggesting query syntax functions, tables and columns.
Tips for tables:
For example for Tables, after you type 'From' and Space it with automatically suggest the existing tables you have on the platform. There you may proceed by scrolling through the list or simply continue typing the name of the table you are interested and the suggestions of the tables will be automatically filtered.
Tips for columns:
- After you type 'Where' statement + hit the space bar a suggestion will be made automatically showing all the columns belonging to that table.
- After you select the table you might go back to the column selection in order to automatically display all the columns of that table. This is an efficient way to save time specially when there is a wide number of columns to be selected.
- You can display the all columns by typing the shortcut __ (double underscores) then the All columns suggestion will automatically appear.
After clicking the option, all the columns will automatically appear, making it easy to keep only columns we need.
- Query syntax suggestions:
In every step of writing a query after any keyboard space typed, a suggestion window will open suggesting query syntax functions, tables and columns. The suggestions are automatically filtered based on the step of the query you are writing.
- Query Generator. By clicking the "Toggle Schema Explorer" button you can view all the table sources available for use. They appear like a tree, and by selecting the specific source table you can view all the table columns.
You can immediately generate a select statement with all the columns if you click the generate query button when you hover to a specific table. You can start working right away with just one click.
- Partial Preview. You have the option anytime to partially preview the query by simply selecting the query part you want to run.
To partially preview simply select the part of the working query, in this moment the "Preview Selected Query" button becomes active. Click the "Preview Selected Query" and the result of the selected query will appear in the right.
3- Preview Query.
After you created your SQL query click Preview Query in order to see the result set table in the right.
4- Data Preview.
After clicking "data preview", the results table will be displayed in the right part of the screen.
5- Add table.
If you want to add new tables (transformations) on the same SQL model you can do so by clicking Add Table Button and then adding the new table name.
6- Tables Panel.
Once you added the new table, it will appear next to the old one in the tables bar.
Here you have the possibility to do new transformations on the new table.
7- Commit and trigger table jobs.
Once you made the changes you wanted to click commit in order to save your changes into the output data.
Note: After the data are committed the button will become non-clickable until new changes happen inside the board.
8- Version History.
By clicking version history you can view the queries run on the SQL model you are running. This makes it easy to track and reuse old versions of working queries.
Here you can:
- Access the Old queries expressions
- Copy the Old queries expressions
- Access the Old queries data result set. You can do this by clicking the data button.
- Start Editing in a new tab, by creating a new table on top of an old query version.
9- By clicking the "Toggle Schema Explorer" button you can view all the table sources
available for use. They appear like a tree, and by selecting the specific source table you can view all the table columns.
You can immediately generate a select statement with all the columns if you click the generate query button when you hover to a specific table. You can start working right away with just one click.
10- Detail view the SQl-Model metadata button.
** By clicking this ** button you can view and edit the model metadata. Here you can access metadata like status, tag, title,description,owners, watchers, experts for this model.
You have also the possibility to add comments or delete the model from here.
You can also view the metadata file history and revert file changes:
11- Columns.
By clicking the columns we have the possibility to group the data by column. You can also remove the columns you do not need, this way your data are cleaner for your analysis.
After you click the column button all the columns will display in a menu bar in the right. if you want to group by the data simply drag the column you want into Row Groups (do so by clicking the two horizontal lines near the column name).
If you want to remove Values you can do so by clicking the X sign.
12- Filters.
When clicking filters button all the columns will display in a menu bar in the right. You might want to filter the column data, without needing to scroll horizontally (when you have too many columns) to find the column you need to filter out. That is when the vertical column listing comes in rescue.
Simply click on the column you need to add a filtering criteria and add the condition.
13- Expand / Minimize Button.
This button helps in making the board higher by hiding the model sections panel. Once you finish with the working board you might need to show the panel again so you jump to the other sections of the model.
