This post is about database system.
Recently, High-performance cloud data warehouse like Snowflake, BigQuery or Databricks have been released and becoming popular. I have often joined the migration project from legacy database to cloud data warehouse. At those projects, I often want to obtain a list of tables in the source database. I think if you are a database administrator you can write SQL normally, but I would like to try using Alteryx. This time I’m using Microsoft’s SQL Server, but similar things can be done with almost any database system
Where is the table list?
Where can I find the table list in the database? Actually, the database has the tables that it manage itself.
For example, those are the tables in sys.objects table in the Microsoft SQL Server. For Snowflake, those are the tables in the INFORMATION schema. For PostgreSQL, those are pg_tables.
Let’s access them using Alteryx Designer. To access them, you can use the Input Data tool. If you are not used to access the databases, perhaps making connection to the db is a little difficult. But it is another story in this post. If you can access the database, you can see the dialogue “Choose Table or Specify Query”. For instance, let’s see the Visual Query Builder.
But you can not see the “sys.objects” table. It seems that it shows the table that the user usually uses in the Visual Query builder. In this case, you need to access what you want using “Tables” or “SQL Editor”. So, let’s change it to the SQL Editor and write the SQL below.
select * from sys.objects
If you do “Test Query” and then there is no error, it is successfull. Click on the “OK” button and close the dialogue. After that, you can get what you want. The sample data is below.
If you use “Tables” tab, you can just select “sys”.”objects” as follows.
The advantages of the SQL Editor are that you can use the SQL statement that you just find on the internet.
Summary
- I explained how to get the database information like table list.
- If you can get the database information, you can do many things using that information. For example, you can make the batch macro to get the sample data from all tables of database, or you can make the document of the table using the table information.
Next Blog post
I would like to write about the Table tool.
コメント