When you want to access to the database in Alteryx, you can access tables of the database directly, but the Visual Query Builder allows you to make SQL using a GUI. It is easier for beginner of the database user than making a SQL by himself.
About the option of connection to the database
When you access to the database by Input Data tool, firstly you have to select the data source and set the database connection, and then the Designer shows the dialogue the below and you can select the way of the database connection from “Table”, “Visual Query Builder”, “Stored Procedures” and “SQL Editor”.
When you select the “Table”, the Designer shows the list of tables, and then you just need to select it. For example, when you select “geotest” table, the Designer create the SQL below.
SELECT * FROM "testdb"."dbo"."geotest"This SQL means that the Designer takes the whole table. If the table has too much data, it takes too much time to obtain data. So, you need to select the data that you only need. For that, you have to create SQL by SQL Editor or Visual Query Builder.
When you use the SQL Editor, you need to create SQL by yourself with no assist.
This tool has only one function that confirms whether the Query is correct or not.
For other hands, the Visual Query Builder allows you to create SQL using GUI.
I remember that the Microsoft Access has the similar function….
When you use it, you can select the columns, filter the data, summarize the data by grouping, join the multiple tables and union the tables.
How to use the Visual Query Builder?
Actually, the manual for the Visual Query Builder is very simple. However I found that it is 3rd party component. It is “Active Query Builder“. The document is here. According to that manual, the function is follows.
- Adding tables
- Selecting columns
- Joining tables
- Union of tables
- Sorting
- Filtering
- Grouping
- Aggregation
When I used the Visual Query Builder at first, I felt that I didn’t need the In-DB tools. However, In-DB tools allow you to visualize how to handle the data. If you need to create a complex SQL, please use In-DB tools for other people who is not used to SQL.
Conclusion
- Explained three options for connecting databases.
- Explained what can be done with a Visual Query Builder
- Showed the document of the Visual Query Builder
Contents of the next blog
The next post will be about the Field Append tool.
コメント