Set up your data source

The Data source setting enables you to specify one or more data sources to perform SQL queries on. This is a fixed list to which you can add as many data sources as you need. You can only add sources when you are setting up a recipe.

1

To add your first and subsequent data sources, select Add data source.

2

Configure the following fields.

Data Source name

Specify an alias name for the data source, which is used to refer to it in the query. For example, accounts, employees, and more.

Data source type

Choose the data source type from where SQL Transformations fetches data. Available options include direct CSV content stream or Workato FileStorage file.

CSV stream input

This option appears when you select CSV content stream as a data source type. Here you can pass the CSV content's datapill from an upstream action. For example, you can choose to pass bulk actions or file contents from a file connector.

FileStorage file path

This option appears when you select FileStorage file as the data source type. Here you can directly key in the path of a file that is stored in FileStorage or pass a datapill from upstream FileStorage actions, including Search files or Get contents.

Data schema

The Data schema is essential to query the data provided. To easily add the data schema, select the Use a sample CSV file option and provide a sample file to fetch the schema of the columns in the file. Alternatively, you can modify the schema later or add the schema completely from scratch.

HOW ARE THE COLUMNS MATCHED IN THE DATA SCHEMA?

The column names provided in the schema input from top to bottom are order-matched to the columns in the actual data from left to right. This ensures that the utility can identify the columns in the correct order even when the actual data does not contain column names.

Workato only throws an error when the number of columns between the schema and the actual data does not match.

Ignore CSV header row

This option is useful to prevent Workato from considering the header row from the CSV contents or CSV file as a data row. By default, the header row is included and not ignored.

Column delimiter

Choose the type of delimiter used in the CSV data. Your data may include a , (comma), ; (semicolon), or other delimiters.

3

In the same way, you can keep adding additional data sources to the Query CSV action by clicking Add data source. This allows you to pipeline all the data that is required for the transformation.

Example data source: CSV file content from AWS S3 connector

In this example the data source is named employee and the data is fetched from file content coming from an S3 download file action. The schema contains employee-related information, and the CSV data uses a , (comma) as the delimiter.

Additionally, the user has chosen to ignore the CSV header row.

Data source example setup 1

Example data source: CSV file stored in Workato FileStorage

In this example the data source is named zipcode and is fetched from a file stored in Workato FileStorage in the path SQL/zipcode_data.csv.

Similar to the preceding example, the column delimiter is a , (comma), and the user has chosen to ignore the CSV header row of the data from the file while running the query.

Data source example setup 2

---

Read next

1. Set up your query
2. Configure your output
3. Output fields

Sample use cases

See our guides for step-by-step instructions on how to leverage SQL Transformations for the following use cases:

• Change data capture
• Data validation and cleansing
• Data enrichment