Skip to content

SQL Query to storage

With this automation, you can store the result of an SQL Query into a Serenytics storage.

Basic Configuration

The configuration is straightforward. You need to provide the credentials to connect to your database.

Then, you can write any SQL query in the editor. The final step is to run the query in the Execution tab.

Options

Quotes all string fields when reloading data

For performances reasons, the result of your SQL query is stored in a CSV file before being reloaded. The default settings will ensure the CSV file is correct and can be reloaded in the destination storage. But in some corner-cases (e.g. if some of your fields contain a Carriage Return character without any LineFeed character), your automation may fail. If you enable this Quotes all string fields when reloading data option, this type of issue will be fixed. But the automation is usually 15% slower.

Force types

By default, the Serenytics engine will auto-detect types. In some corner-cases, you may want to force the types (e.g. when the type detection fails). This field lets you manually force the types of the columns in the output storage.

Example:

{"mycolumn1": "int32", "Mycolumn2": "float64", "Mycolumn that should be a date": "datetime"}

The provided types must be one of int32, int64, float32, float64, datetime, str, str(X).

  • int64 is an integer on 64 bits (8 bytes), also referred as BigInt in some databases.
  • float64 is a double precision float.
  • str(X) is a varchar column with length X.

Which column name to use

You may have noticed that the output storage column names may be slightly different from your sql query column names. When you specify the forced type of a column in this automation, you need to use the column name of the SQL query, and not the column name in the output storage. For example, if you have a date of creation column in your query, the output column name will be date_of_creation in the storage. In the Force types field, you need to use the date of creation name.

Flow mode

The default flow mode is Full. In this mode, the destination storage will be emptied, and then, all the rows obtained from the query will be stored into the storage.

In Append mode, all the rows obtained from the query will be stored in the storage, without dropping the existing rows.

In Update with key, for each row from the query: if the key does not exist in the storage, the row will be copied into the storage; if the key already exists in the storage, the row from the query will overwrite the current row. In this mode, you need to provide the column name of the key. This key must be unique for each row.