Flat File (SQL)
The Flat File (SQL) connector allows querying and transformation of flat file data using SQL syntax. This enables the integration of flat file data sources into SQL-based workflows for data manipulation and analysis.
important
Using this connector requires an add-on license, included in the custom connectors package.
Prerequisites
Before connecting, ensure the following prerequisites are met:
- Verify that the storage or folder location is accessible from the cluster.
- Gather valid user credentials and ensure the user has appropriate permissions to read the files.
- Ensure at least one file is present in the folder.
Connecting to Amazon S3
AWS Custom Credential Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Flat File (SQL) Custom JDBC |
| Container * | The name of the S3 bucket that contains the flat files. | acme-prod-bucket |
| Folder Path * | The directory path within the bucket that points to the folder containing flat file SQL files. | /data/prod-files/ |
| Region * | The AWS region where the S3 bucket is located. | us-east-1 |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Access Key * | The key used to authenticate the connection to the storage account. | fnsdt8765dfyjm67i5mnvfhcn56858 |
| Secret Key * | The secret key used to authenticate the connection to the storage account. | wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY |
| Session Token * | Temporary token used for session-based authentication. | FQXdzEJr/////wEaDKr2EjTokenjd81nSTEXAMPLETOKEN== |
warning
The Access and Secret keys must be updated whenever the session token expires.
AWS EC2 Role Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Flat File (SQL) Custom JDBC |
| Container * | The name of the S3 bucket that contains the flat file SQL files. | acme-prod-bucket |
| Folder Path * | The directory path within the bucket that points to the folder containing flat files. | /data/prod-files/ |
| Region * | The AWS region where the S3 bucket is located. | us-east-1 |
| Type * | For EC2 role authentication, the connection type must be a System Connection. Refer Connections for more details. | System |
AWS IAM Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Flat File (SQL) Custom JDBC |
| Container * | The name of the S3 bucket that contains the flat file SQL files. | acme-prod-bucket |
| Folder Path * | The directory path within the bucket that points to the folder containing flat files. | /data/dev-files/ |
| Region * | The AWS region where the S3 bucket is located. | us-east-1 |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Access Key * | The key used to authenticate the connection to the storage account. | eargaerh5235fjhj89789sf23fsd |
| Secret Key * | The secret key used to authenticate the connection to the storage account. | wEU3mK30P4EjEXAMPLETOKEN== |
Connecting to Azure Blob Storage
Azure Access Key Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Flat File (SQL) Custom JDBC |
| Container * | Name of the storage container where the flat file SQL files are stored. | acme-prod-bucket |
| Folder Path * | The directory path within the container that points to the folder containing flat files. | /data/prod-files/ |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Account Name * | The storage account name used for authentication. | Flatfilesqlstorageaccount |
| Access Key * | The key used to authenticate the connection to the storage account. | eargaerh5235fjhj89789sf23fsd |
Azure Service Principal Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Flat File (SQL) Custom JDBC |
| Container * | Name of the storage container where the flat file SQL files are stored. | acme-prod-bucket |
| Folder Path * | The directory path within the container that points to the folder containing flat files. | /data/prod-files/ |
| Type * | For service principal authentication, the connection type must be a System Connection. Refer Connections for more details. | System |
| Account Name * | The storage account name used for authentication. | Flatfilesqlstorageaccount |
| Tenant ID * | The Microsoft Entra ID (formerly Azure AD) tenant ID used for authentication. | 5684-90ab-dtfg-5374-452986srtki |
| Client ID * | The Application (client) ID registered in Microsoft Entra ID. | rtjstfhbn765-shaq-2379-fmfd-57934657 |
| Client Secret * | The secret/password associated with the registered client app in Microsoft Entra ID. | kouoaqw789-s789s07sdbgh=srsrhhdgju,hs |
Azure Shared Signature Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Flat File (SQL) Custom JDBC |
| Container * | Name of the storage container where the flat file SQL files are stored. | acme-prod-bucket |
| Folder Path * | The directory path within the container that points to the folder containing flat files. | /data/prod-files/ |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Security Token * | The shared access signature (SAS) token used to authenticate access to Azure Blob Storage. | sv=2:59....http&sig=signature |
| Account Name * | The name of the Azure storage account hosting the Blob Storage container. | prodstorageaccount |
Connecting to FTP
Anonymous Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Flat File (SQL) Custom JDBC |
| Host * | IP address or hostname of the FTP server. | ftp.acme.com or 193.164.68.41 |
| Port * | The port on which the server listens. Default: 21 for FTP. | 21 |
| Folder Path * | The directory path within the server leading to the folder containing flat files. | /data/acme-files/ |
| Type * | For anonymous authentication, the connection type must be a System Connection. Refer Connections for more details. | System |
Username and Password Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Flat File (SQL) Custom JDBC |
| Host * | IP address or hostname of the FTP server. | ftp.acme.com or 193.164.68.41 |
| Port * | The port on which the server listens. Default: 21 for FTP. | 21 |
| Folder Path * | The directory path within the server leading to the folder containing flat files. | /data/acme-files/ |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Username * | FTP login username with necessary privileges. | ftp_user |
| Password * | Password associated with the specified username. | Str0ngP@ssw0rd2025! |
Connecting to Google Cloud Storage
Service Account Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Flat File (SQL) Custom JDBC |
| Project ID * | The unique identifier of the Google Cloud project where the storage bucket resides. | acme-gcp-project-5726 |
| Container * | Name of the storage bucket where the flat file SQL files are stored. | prod-data-container |
| Folder Path * | The directory path within the bucket that points to the folder containing flat files. | data/flatfilesql-files/ |
| Type * | For service account authentication, the connection type must be a System Connection. Refer Connections for more details. | System |
Connecting to Local Storage
Users can read files from local storage, which may refer to a server directory for uploaded files or a network-mounted directory accessible to the cluster. Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Flat File (SQL) Custom JDBC |
| Folder Path * | The directory path leading to the folder containing the files. | /data/flatfilesql-files/ |
warning
This connector does not support authentication for accessing files from local storage.
Connecting to SFTP
Username and Password Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Flat File (SQL) Custom JDBC |
| Host * | IP address or hostname of the SFTP server. | sftp.acme.com or 193.164.68.41 |
| Port * | The port on which the server listens. Default: 22 for SFTP. | 22 |
| Folder Path * | The directory path within the server leading to the folder containing flat files. | acme/data/files/ |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Username * | SFTP login username with necessary privileges. | reporting_user |
| Password * | Password associated with the specified username. | Rep0rt!ng2025$ecure |
Custom Properties
The following optional connection properties can be configured based on user requirements:
| Property | Default Value | Possible Values | Description |
|---|---|---|---|
| Charset | UTF-8 | UTF-8 | Specifies the session character set for encoding and decoding character data transferred to and from the flat file. |
| RowScanDepth | 100 | Integer value | The maximum number of rows to scan to detect the available columns in a flat file. |
| SkipHeaderComments | false | true, false | Indicates whether lines beginning with comment characters (e.g., #, //) in the header should be skipped. |
| Timeout | 60 | Integer value | Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. |
| TypeDetectionScheme | None | None, RowScan, ColumnFormat, ColumnStyle | Determines how the provider detects the data types of columns. |
Supported Data Types
The following data types are supported:
- INTEGER
- BIGINT
- SHORT
- FLOAT
- VARCHAR
- CHAR
- BOOLEAN/BIT
- DATE
- DATETIME
- TIME
- TIMESTAMP
Unsupported Data Types
The following data types are not supported:
- BINARY
- ARRAY
- STRUCT
Related Articles
The following resources provide additional guidance on working with Flat Files in iceDQ.
| Description | Link |
|---|---|
| Query Delimited File | View Article |