WeeklyAlteryxTips#93 Handling large amounts of data when using a database

Alteryx

This blog series, titled “AlteryxTips,” will feature helpful Alteryx tips delivered weekly by AlteryxACE AkimasaKajitani.

This article is intended for users who work with databases. When a database contains a very large amount of data, processing it can be challenging. Simply accessing the database and retrieving the data may take a considerable amount of time, so let’s look at ways to address this issue.

How to Handle Large Volumes of Database Data

When a database contains a large amount of data and Alteryx connects to a table, the SQL statement will look like this:

SELECT * FROM [tablename];

This retrieves every record. However, in many cases you may only need an aggregation, a limited range of data—such as data for a particular month—or otherwise not require every record in the table. Filtering or summarising the data when retrieving it can reduce the volume transferred, particularly for data warehouses such as Snowflake and BigQuery.

Alteryx provides two ways to address this:

  1. Use SQL statements
  2. Use In-DB

1. Use SQL Statements

To handle this with SQL, either write SQL directly in the SQL Editor or use the Visual Query Builder. The Visual Query Builder is easy to use so it’s good for beginners, although complex SQL can be difficult to create with it.

For an explanation of the Visual Query Builder, please refer to the previous article.

You can also write SQL freely in the SQL Editor. Asking ChatGPT or Gemini to generate a query may be another option. However, you must verify that the result works as intended, and this may be difficult without any SQL knowledge.

2. Use In-DB

Next, let’s look at how to use In-DB.

In-DB means processing within the database. Normally, Designer loads all data into the memory of the PC on which it is running. In-DB instead lets the database perform the processing, which can improve speed and reduce the data volume before bringing results into Designer.

To use In-DB, select tools from the “In-Database” category in the Designer tool palette. Native mode in Designer Cloud(now it’s Live Query) operates entirely in In-DB mode.

In-DB機能を使うためには、Designerのツールパレットの「インデータベース」カテゴリのツールを使います(ちなみに、Designer CloudのNativeモードはすべてIn-DBで動いています)。

To read data from a database, use the Connect In-DB tool.

Because the data remains in the database, running Connect In-DB finishes almost instantly: no results are output to Designer. To inspect data, use Browse In-DB. By default, it displays only the first 100 records, so it usually has little impact on execution time, although large aggregations may take longer.

Tools in the In-DB category includes Select, Sample, Join, Summarize, Union, Filter, and Formula. Designer generates SQL and sends it to the database, so these tools are SQL-based. Formula In-DB expressions must use functions supported by the connected database, requiring some SQL knowledge. The main differences from standard tools are as follows:

  • Fewer types of aggregation are available.
  • Joins correspond to SQL inner, left, right, and full outer joins.
  • All expressions must use functions supported by the connected database.
  • Operations such as changing data types are not available.

After reducing the data through aggregation or filtering, use the Data Stream Out tool to continue processing with standard Designer tools. It has a blue anchor for In-DB tools and a light-green anchor for standard tools.

This brings results processed with In-DB directly into Designer. For example, you can create a workflow like the following.

The reverse-pattern Data Stream In tool sends Designer data into the database for In-DB processing.

Using In-Database (In-DB) tools makes it less likely to get bogged down in complicated SQL that’s hard to understand, offering an advantage in terms of process visibility compared to writing SQL directly. However, In-DB tools tend to generate slightly more complex SQL. Therefore, if you only consider processing speed, writing SQL directly might be faster, but the best approach depends on whether you prioritize maintainability or clarity.

Improve Execution Speed During Workflow Development

When building a workflow, repeatedly retrieving large amounts of data loads the database, takes time, and may incur charges with usage-based pricing. In such cases, cache the data after retrieving it once.

There are two methods for that:

  1. Use “Cache and Run Workflow”
  2. Save the data as a YXDB file

1. Use “Cache and Run Workflow”

Right-click a tool and select “Cache and Run Workflow” to cache processing up to that tool. Data loading and prior processing are skipped, and the remainder runs using the cached data. Please note that In-DB tools cannot be cached; caching is only possible with Data Stream Out tools and later.

For example, right-click the Data Stream Out tool in the workflow introduced here.

From this menu, click “Cache and Run Workflow.”

When you run the workflow with “Cache and Run Workflow”, the portion up to the selected tool is cached and its settings cannot be changed. Only the uncached portion is executed.

This is convenient, but caching sometimes may not be available for complex workflows. The cache is cleared when you restart Designer or reload the workflow.

2. Save the Data as a YXDB File

Place an Output Data tool immediately after an Input Data or Data Stream Out tool and save the data in YXDB format to create your own cache file. YXDB is an Alteryx-specific format with very fast read and write performance.

In this case, place the sections you do not want to run—the Input Data tool or In-DB tools—inside a Tool Container and disable it. Convert the Output Data tool into an Input Data tool by right-clicking it.

Then use a Union tool to create a workflow like the following.

When switching to production, delete the cache Input Data tool or place it in a container and disable it. If development is complete, deleting it is preferable.

Conclusion

  • We explained how to handle large volumes of data in a database.
    • You can use In-DB or write SQL directly.
    • I also introduced caching and saving data in YXDB format for use as a cache.

Next post will be…

It will be about Workflow Group.

コメント

Copied title and URL