Working with Databases
Accessing databases from Alteryx is very simple and fast. And the methods we use for files can apply to databases as well.
But databases have more peculiarities, many of which we, as analysts, cannot change (such as response speed, availability, and design).
Also, we’ll be addressing the basics of Data Connection Manager (DCM), a very useful and powerful feature introduced by Alteryx in version 2021.4, but highly improved in version 2022.1.
DCM is a secure, centralized, single-source administration, storage, and connection-sharing capability for database and cloud interoperability, offering enhanced security improvements (credentials linked to data sources and resolved at runtime).
If you are an administrator within your company, you have probably already identified the huge benefits DCM brings to your job. If you’re not, you’ll realize how easy it is to administer your credentials and connections using DCM once you start using it.
Another powerful feature of Alteryx Designer is the In-Database (In-DB) tools. These tools allow us to perform blending and analysis against large sets of data without moving the data out of the database, providing performance improvements over the traditional methods, since everything is executed within our Database Management System (DBMS) and no traffic along the network is required (very low to no latency).
In this chapter, we’ll be looking at some recipes to improve how we work with databases:
- Scanning databases dynamically (cursor behavior, but more efficient)
- Using Alteryx Calgary Databases
- Creating credentials in DCM
- Creating connections in DCM
- Getting information from your In-DB connections/queries
We created a portable database (using SQLite) as a test set for these recipes, but if you want to try them with your own data, you’ll need to have your connection information and access credentials at hand.
For the Calgary recipe, make sure you have enough free disk space (~2GB) on your computer.
Even when it’s not required for the recipes, access to Alteryx Server will be needed in case you want to synchronize DCM against your existing enterprise credentials.
Cursor behavior, but more efficient
When working with databases, we call cursor to the process where, for each record of a given table, you need to sequentially scan/read all the records from a second table, in search of a condition.
This process is very useful for some use cases, but it might cause a huge overhead for the database management system and the network. For example, a cell phone provider company has all the data about each call – each IMEI for a period of time – and the marketing department is trying to predict the effects of a certain campaign on some customers.
If we analyze the amount of data produced by each call per phone, it’ll be huge and it’ll take us a lot of time. So, in this case, we probably will extract from the database the data associated with those customers targeted by the campaign first, then analyze it.
For that, we’ll have a first input consisting of the conditions the targeted audience must fulfill, and we use that input data to scan and retrieve each record from the transactional data source (calls in this case) associated with the selected ones.
In this recipe, we’ll learn how to perform a “cursor-like” reading of tables (for each record in one table, read all the records in a second table), using the Dynamic Input tool, avoiding the overhead, and not capturing the database’s server resources.
This set contains a database with three tables:
DOCUMENTS: Containing all the information about a company’s billing (~254K records)
ARTICLES: Containing a description of each
ARTICLE_IDavailable for the company
Figure 2.1: Database structure
The use case will be as follows: we, as a hardware store, need to gather the data corresponding to our top 10
CUSTOMERS from last year and get the top 10
ARTICLES each one bought.
DOCUMENTS (billing data) in one table,
ARTICLES in another, and
CUSTOMERS in a third one.
And our top 10
CUSTOMERS from last year come in an Excel File (
How to do it…
- On a new workflow, drop an Input Data tool and point it to
- Select the
2021Top10worksheet in Select Excel Input and click OK.
Figure 2.2: Select Excel Input
- Drop a Dynamic Input tool (from the Developer category) and configure it as follows:
- Click on Edit… for the Input Data Source Template option.
Figure 2.3: Dynamic Input tool configuration options
Figure 2.4: Dynamic Input template configuration
- For the Connect a File or Database option, point it to the SQLite file. When prompted with Choose Table or Specify Query, click on the SQL Editor tab at the top of the window and write this SQL sentence:
SELECT * FROM DOCUMENTS WHERE CUSTOMER_ID=1234 AND PERIOD=2022
This can be seen here:
Figure 2.5: Dynamic Input template query
As you may notice, there is no
CUSTOMER_ID=1234 in the database, but here is where Alteryx Designer will operate its magic.
Figure 2.6: Template panel after the configuration
Now, we need to configure the action we want the tool to perform.
- Select Modify SQL Query, and click Add on the right of the configuration panel. You’ll be presented with five options. Select SQL: Update WHERE Clause:
Figure 2.7: Modify SQL Query options
Figure 2.8: Configuring the Dynamic Input tool
- Make sure
CUSTOMER_ID=1234is selected for SQL Clause to Update, Value Type is set to Integer, Text to Replace is
1234and Replacement Field shows CUSTOMER_ID and click OK.
If you run the workflow, you’ll get all records for 2022 corresponding only to the customer IDs contained in the control file (top 10 buyers from the previous year). From here, you can start the process of getting the top 10 articles bought by each customer, but that will be part of another recipe.
How it works…
When configuring the Dynamic Input tool to any of the Modify SQL Query options, Alteryx Designer will read all the conditions within the query and will replace the parts you indicated within your selections. In this case, since SQL: Update WHERE Clause was selected, Alteryx will modify only the part corresponding to the
For the second part of the clause (
PERIOD=2022), since we didn’t select any modifier for it, it’ll remain untouched.
The amazing part is that Alteryx Designer will execute one straight query per record coming from the Input Data tool, so, instead of having a cursor scanning the database per record in the input file (a single process from start to finish), there’ll be N individual queries running one after the other, causing the release of resources in the DBMS after each query.
Figure 2.9: Multiple queries executed from just one tool
Of course, you can combine multiple
WHERE statements, and replace the part you need with incoming data every time you have to.
But, if you look at Figure 2.7, you have other options to make your database queries dynamic, such as replacing strings in queries, which can be very helpful for executing queries along different tables:
SELECT * FROM "TABLE" WHERE XXXX
You can set up a rule to indicate the tables you want to query, and in the
WHERE clause, the conditions to query those tables, and all can be dynamic.
Working with Calgary databases
Calgary is a list count data retrieval engine designed to perform analyses on large scale databases containing millions of records. Calgary utilizes indexing methodology to quickly retrieve records. A database index is a data structure that improves the speed of data retrieval operations on a database table. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random look ups and efficient access of ordered records.
Besides the actual definition, we can see Calgary as a proprietary file format, with the ability to handle huge amounts of data (~2B records) and to index the contents, so searches are extremely fast because there’s no need to read all the records before filtering them.
- Calgary Input: We’ll use this tool to query Calgary databases
- Calgary Join: It’ll allow us to take an input file and perform join queries against a Calgary database
- Calgary Cross Count: Performs aggregations across multiple Calgary databases and returns a count per record
- Calgary Cross Count Append: This will allow you to take an input file and append counts to records that join a Calgary database when those records match your criteria
- Calgary Loader: This is the tool we’ll use to create/load data into a Calgary file (
We built a test set for this recipe you can download from here:
If you’re planning to follow along with your own data, make sure you have a decent number of records in your dataset (millions).
In both cases, make sure that you have at least 2 GB of available disk space on your computer.
How to do it…
There are two phases in this recipe:
- Creating/loading our data into a Calgary database
- Consuming the loaded data
To create/load the data, we will use the following steps:
- Drop an Input Data tool on the canvas and point it to the
- Immediately, you’ll be prompted to select which file/s to read from the ZIP file and the type of the files. In our example, there’s just one, so select it and make sure that Select file type to extract is set to Comma Separated Value (*.csv) and click Open.
Figure 2.10: Read a file from a ZIP file
- Go to the Input Data tool configuration panel and make sure you change option 9, Delimiters, from a comma (
,) to a pipe (
Figure 2.11: Input Data configuration panel – Delimiter option
Figure 2.12: Contents with the wrong delimiter
It will change to this:
Figure 2.13: After selecting the right delimiter for our file
- Add a Select tool to the canvas, and from the Options menu, click Save/Load and Load Fields Names & Types.
Figure 2.14: Shaping the data types based on saved configurations
- Point to where you saved the recipe test set
FIELD_TYPES\CitibikesFieldConfiguration.yxftand your Select tool will be populated with the field definitions saved in that file.
Figure 2.15: Resulting shape of our data
Figure 2.16: Calgary Loader configuration panel
- Point Root File Name to the folder you want to save your files in and give the file a name. As a best practice, consider using a single folder per Calgary set of files.
Run the workflow and you’ll see that the file is being created and the data is being indexed. While loading and indexing, Alteryx analyzes the contents of our data (the first million records), selecting the best type of index based on the values contained within it.
Figure 2.17: Results of running the workflow
By now, we’ll have the files (one
.cydb and one
.cyidx per indexed field, plus
SelectedName_Indexes.xml containing the index values).
- Static: Using the Calgary Input tool, you can define your query within the tool configuration panel
- Dynamic: Based on a data stream, you can query your Calgary database dynamically using conditions
We can build a static query as follows:
- Drop a Calgary Input tool onto the canvas.
- Point the Calgary Data File option to the
Citibike.cydbfile we just created.
Once you point to the file, the tool’s configuration panel will show you the options for building the query:
Figure 2.18: Calgary Input configuration
- From the
BIRTHgroup, double-click on the
YEARfield. Alteryx will pop up a new window – Edit Query Item.
Figure 2.19: Setting the query item
- We need to get a range starting at any value, but only up to 1963. So, uncheck Include Begin, check Include End, and enter
1963for the end value.
Figure 2.20: Using only range end
- Click OK.
Figure 2.21: Query clause in the configuration panel
- Drop a Browse tool following the Calgary Input tool and run the workflow. You’ll be able to see all data regarding trips made by people 50 years or older.
We will now dynamically query a Calgary database:
We are going to query the Calgary database for the same results but using a different approach. We’ll be getting some input from a data stream and using those values to query/join against the Calgary database:
- In this case, we are going to use the age limit as an input, so drop a Text Input tool onto the canvas.
- Create a column called
AGE_LIMITand add a record with
50as the value.
Figure 2.22: Incoming data
Since the input data we have is the minimum age to consider (remember, we are going to get rides done by people 50 years old or more), we need to transform it, so we can query our data based on
- Connect a Formula tool to the Text Input output anchor, and create a new field called
MAX_BORNwith the following expression to determine which year is the maximum to query (remember that data is from 2013):
- Add a second column called
MIN_BORNwith the value
1000(to ensure all data that represents any year before
Your Formula tool must look like this:
Figure 2.23: Formula to determine the range
Figure 2.24: Input data enriched
- Connect a Calgary Join tool to the Text Input tool and point Calgary Data File to
- Select Join Query Results to Each Input Record for the Action option.
- Click on the
MIN_BORNinput field and select BIRTH_YEAR for Index Field, Range - >=Begin AND <=End for Query Type, and
MAX_BORNfor End of Range, as in the screenshot here:
Figure 2.25: Calgary Join configured
How it works…
Calgary is a proprietary format developed by Alteryx that provides very high compression and very fast reading performance and indexing, making it ideal to work with huge amounts of data for lookups.
We recommend always enriching your data as much as you can before creating a Calgary file (very similar to what you’ll do when you create a multidimensional cube). For example, given the use case we used in this recipe, we’ll probably add the age of each person in the Calgary database when creating it, so we can use the Calgary Join tool directly on the
Figure 2.26: Calgary Join actions
You can’t append records to a Calgary database, you need to re-create it.
As you already may have noticed, the Calgary Input tool organizes the fields based on their names, so for example, for all the fields starting with
END_, it created a group that has all the fields starting with
END_ in it (such as
Figure 2.27: Fields grouped by prefix
It is good practice to add a prefix to your fields to have them organized.
Since Alteryx looks at the first million records to select the index type when set to Auto, it might select the incorrect type for your dataset. It’s a good practice to analyze your data first and determine the selectivity of each index, based on the number of different values each data field might have. This can be easily achieved using a Summarize tool configured to perform a Count Distinct action on each field to be indexed.
Figure 2.28: Count Distinct on each field to Index
- If your field has many possible values (more than 550), use High Selectivity (for example,
- If your field has fewer unique values (less than 550), use Low Selectivity (for example,
Doing this will also reduce the time Alteryx Designer needs to analyze your data (1 million records per index) and create the indices.
Finally, another good practice, that’ll make your work easier is adding flags or identifiers to the data before loading a Calgary database, such as a
CURRENT_PERIOD field to easily query all records corresponding to the current period, or a
SAME_PERIOD_LAST_YEAR field to get all the records corresponding to a particular period, but from last year.
You can also read Calgary files with a regular Input Data tool, but can’t take advantage of the indices (so the Calgary files will behave like a
DCM – setting up credentials
As we saw in this chapter introduction, DCM allows you to administer credentials and passwords in a single-source, centralized way, so it solves some pain points, for example, multiple credential inputs, credentials being unsafely shared, loss of connection to data sources upon workflow sharing, among others.
Before getting into the matter, we need to identify three types of objects/concepts within DCM:
- Credentials: Authentication mechanism for the specific technology
- Data Sources: All accessible technologies supported by Alteryx
- Connections: The combination of a data source and the credentials used to validate within
Also, if you have Alteryx Server, you can synchronize and share your connections against it. If you don’t, credentials, data sources, and connections created with DCM will remain local.
To follow this recipe, you must enable DCM on Alteryx Designer. To do so, go to Options → User Settings → Edit User Settings and from the DCM tab click on Enable DCM.
If the Enable DCM option appears disabled to you, click first on Override DCM System Settings, and it will enable it.
Figure 2.29: DCM options in User Settings
Restart Alteryx Designer and you’ll be ready to work with DCM.
How to do it…
We will get started using the following steps:
- Go to File → Manage Connections.
Figure 2.30: Manage Connections menu
Figure 2.31: DCM main window
- Click on + Add Credential at the top right of the window and Alteryx will ask you to enter values for Credential Name and Method.
Figure 2.32: DCM main window
- Enter a meaningful name for your credential, such as
SQL SERVER System Administrator, and select from the dropdown for Method. In this case, we’ll be using Username and password.
Figure 2.33: Credential Method options
- Once you make a selection for Method, Alteryx will show you the Username and Password input fields, so fill them in with your credentials.
Figure 2.34: Credential Method options
Click Save and your credential will appear in the Credentials panel.
Figure 2.35: New credential added
Thus, we have learned how to set up credentials using DCM.
How it works…
This actually improves the way that credentials are managed, since using DCM changes how that information is saved (if DCM is disabled, credentials are embedded within the workflow).
DCM – setting up a connection
In this recipe, we’ll be creating a new connection using DCM capabilities.
We’ll prepare to do this using the following steps:
- If you’ve already enabled DCM on Alteryx Designer you can skip this next step, otherwise, you need to do it to make DCM available for you. To do so, go to Options → User Settings → Edit User Settings and from the DCM tab click on Enable DCM.
Figure 2.36: DCM options in User Settings
- Make sure DCM Optional is the selected value for DCM Mode and SDK Access Mode is set to Allow.
- Restart Alteryx Designer and you’ll be ready to work with DCM.
If you have access to Alteryx Server, you’ll be able to synchronize your local and remote connections with it.
Also, make sure you have access to at least one database from any of the technologies supported by Alteryx.
This synchronization process is manual and can only be triggered from Alteryx Designer.
How to do it…
- Go to File → Manage Connections.
Figure 2.37: Manage Connections menu
A new window is displayed (yours might be blank).
Figure 2.38: DCM main window
- Click on Add Data Source at the top right of the new window, so the Select Technology option shows up.
- From the dropdown, select the type of technology you will be connecting to (see the complete list of tools and technologies supported by DCM here: https://help.alteryx.com/current/designer/dcm-designer):
Figure 2.39: Technology selection for new connections
For this recipe, we’ll be using Microsoft SQL Server Quick Connect, but feel free to select the technology you want. The steps will be the same – what will change is the data you need to enter to connect to that technology.
- Enter your connection’s specifics and click Save.
Figure 2.40: Setting up a SQL Server connection
- Now, we need to link the credentials with the data source object to create a connection.
Figure 2.41: Linking credentials to a connection
- Click on + Connect Credential and the panel will change, so you can select the type of credentials (Authentication Method) you’ll be using for this connection.
Figure 2.42: Selecting Authentication Method for linked credentials
Depending on your selection, Alteryx will filter and show all credentials of the selected type for you to choose.
Select Username and password and you’ll see that a new field was added to the panel, with a dropdown to select from all existing username and password credentials.
Figure 2.43: Selecting the credentials
- Select the one we created in recipe #3 (
SQL SERVER System Administrator) and click on the Link button.
Figure 2.44: Linked credential
Now we have our connection ready to be used.
How it works…
DCM allows us to create credentials and data sources. Those objects can be individually administrated in a centralized secure space. The combination of a data source and a set of credentials gives us a connection object that we can use in our workflows without caring about logins and server names.
If you use DCM, every change you make to a connection will be picked up by your workflows. So, for example, you need to change your password once (in DCM’s Connection Manager) and all workflows using that credential will get updated.
Figure 2.45: Using DCM and without using DCM
See the complete list of tools and technologies supported by DCM here: https://help.alteryx.com/current/designer/dcm-designer
Getting information from your In-DB connection/query
When working with In-Database tools in Alteryx, and probably using the Visual Query Builder, queries are built from within the tools by Alteryx and sometimes we’ll need to take those queries and have somebody optimize them for us or test them outside Alteryx.
The Dynamic Output tool allows us to get a lot of information about what and how Alteryx queries our databases.
Throughout this recipe, we’ll be exploring how to get that information and how we can make use of it.
To practice this recipe, we created a test set that you can download from here:
Before starting with the recipe, just make sure that you install the SQLite ODBC driver (in the
\SQLITE-ODBC folder). If you are on 32-bit Windows, use
sqliteodbc.exe and if you are on 64-bit Windows, use
sqliteodbc_W64.exe for the installation:
- Once installed, go to the ODBC data sources corresponding to the actual version of your OS (32- or 64-bit).
Figure 2.46: ODBC Data Source Administrator
Figure 2.47: Selecting a driver for the data source
- On the new screen, give your connection a name.
Figure 2.48: SQLite3 driver configuration
- Click on the Browse… button and select where you saved the provided SQLite database (it should be in
If you plan to use your own data, you’ll only need to have access to a database you can query.
How to do it…
We are going to get the total billed amounts per customer. For this, we have three tables:
DOCUMENTS table has all the information about the billing (including the amount in the
TOTAL field) but has no details about customers or articles (just an ID). So we need to join the tables to get those details.
Figure 2.49: Structures of the tables
- Grab a Connect In-DB tool from the In-Database category and drop it onto the canvas.
- From the tool configuration panel, click on Manage Connections to create an Alteryx In-DB connection.
Figure 2.50: In-DB connection
The Manage In-DB Connections screen will pop up, allowing you to start configuring the new connection.
- From the Data Source dropdown, select Generic ODBC (we’ll be pointing it to the ODBC data source we created earlier).
- For the Connection Type dropdown, leave it at User and click the New button for Connections. This will enable the Connection Name field, so give the connection a name (we used
SQLITEas you can see in the following figure).
Figure 2.51: In-DB connection
Figure 2.52: New database connection…
- This will make the ODBC Connection screen pop up. From here, select the AlteryxCookbook connection (the one we created in the Getting ready part of this recipe) and click OK.
Figure 2.53: Selecting which ODBC data source to use for the current connection
- Click OK on the Manage In-DB Connections window, and the Choose Table or Specify Query window will pop up showing existing tables within the actual connection (by default, it’ll open in the Tables tab).
- Click on the Visual Query Builder tab so you can start building a query using drag and drop.
Figure 2.54: Visual Query Builder
- Drag the
DOCUMENTStable and drop it into the Main canvas.
- Repeat the operation for the
- From the
DOCUMENTStable, drag the
COMPANY_IDfield and drop it over the
Repeat the procedure, linking the following:
- Now click on the first checkbox in the
DOCUMENTStable to select all fields from it (
*), and select
CUSTOMERS.EMAIL, checking the checkbox of each of these fields.
Your query should look like this:
Figure 2.55: Completed query in Visual Query Builder
- Click OK and you’ll return to the Alteryx Designer canvas.
Now, to get the total amounts per customer, we need to summarize, grouping by
CUSTOMER_ID, and get
- Drop a Summarize In-DB tool onto the canvas, and configure it as shown in the following figure, so the tool’s configuration panel looks like this:
Figure 2.56: Summarize In-DB
Figure 2.57: Workflow results
At this point, we need to see how Alteryx resolves the queries we created in its drag-and-drop interface, and we can extract that information using a Dynamic Output In-DB tool.
- So, connect a Dynamic Output In-DB tool to the output anchor of the Summarize In-DB tool, and a regular Browse tool to the output anchor of the Dynamic Output In-DB tool.
At this point, your workflow should look like the following figure:
Figure 2.58: Our workflow
- Click on the Dynamic Output In-DB tool and select all output fields, except for Input Connection String and Output Connection String.
Figure 2.59: Dynamic Output In-DB output fields
- Run the workflow and review the resulting fields.
Figure 2.60: Results for Dynamic Output In-DB
The following fields can be found here:
- Query: This is the complete query generated up to this point in the workflow.
- Connection Name: The name of the Alteryx connection you’re using (comes from the name you gave it when you created it).
- Connection Data Source: This is the database type. Note that since we used a generic ODBC type of connection, that value is not available to Alteryx – that’s why we get Unknown here.
- In-DB XML: The Alteryx XML representation of the query.
- Record Info XML: The XML representation of the query fields.
- Query Alias List: This contains each segment of the query and the ID Alteryx gave to them.
- Last Query Alias: The last alias from the list.
From the Query field, you have access to the SQL query created by Alteryx Designer – in our case, the following:
WITH "Tool1_fc91" AS (select DOCUMENTS.*, ARTICLES.DESCRIPTION, CUSTOMERS.FIRST, CUSTOMERS.LAST, CUSTOMERS.EMAIL from DOCUMENTS inner join ARTICLES on DOCUMENTS.COMPANY_ID = ARTICLES.COMPANY_ID and DOCUMENTS.ARTICLE_ID = ARTICLES.ARTICLE_ID inner join CUSTOMERS on DOCUMENTS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID) SELECT "CUSTOMER_ID", MIN("FIRST") AS "FIRST", MIN("LAST") AS "LAST", MIN("EMAIL") AS "EMAIL", SUM("TOTAL") AS "Sum_TOTAL" FROM "Tool1_fc91" GROUP BY "CUSTOMER_ID"
From the Query Alias List field, we can access the different sub-queries created to that point within the workflow.
At this point, we can save or copy that information to analyze and further optimize our queries.
How it works…
Creating queries in a visual interface is easier than writing code, and not all of us are able to do SQL scripting. The Visual Query Designer gives us the ability to create complex queries without any programming knowledge, but sometimes we’ll need assistance in optimizing those queries.
The Dynamic Output In-DB tool provides us with ease of access to the generated queries that Alteryx executes against our database management systems, by registering and extracting that information for us.
Figure 2.61: Configuring the relationships
See also (follow-up steps)
The Connection Name field and the Query or Query Alias List fields extracted from the Dynamic Output In-DB tool can be used to generate dynamic and/or batch queries using a Dynamic Input In-DB tool connected to a data stream.