Writing SQL Queries: Delimted Files – Customer Support

NOTE: This article discusses use of QuerySurges new Delimited connection and not our legacy Flat File connection.

QuerySurge supports reading Delimited files using a JDBC driver that is deployed automatically. Using this driver data testers can use familiar SQL syntax to retrieve data from Delimited files and validate it against any QuerySurge supported Source or Target.

Note: All files referenced in this article are available for download in the Resources section at the end of the article.

Connection Setup

Delimited File connections can be set up in the QuerySurge Admin view, and can be utilized in the same manner as any other Connection types (i.e. in QueryPairs and Staging Queries, and in Test Suite Connection overrides).

For details on setting up a Connection to Delimited File, see Configuring Connections: Delimited File

Querying Delimited Files

The Delimited File JDBC driver supports a SQL syntax that is familiar to users of relational databases such as PostgreSQL, SQLite, and MySQL. The main difference is in the FROM clause, where a special function named read_csv is used to load data from a Delimited file. Below is an example of this syntax where filename is the full file path to the Delimited file being queried.

SELECT * 
FROM read_csv(filename);

Note: Although named read_csv, this function can read any delimited text file, not just CSV files. It supports formats such as TSV (tab-delimited), semicolon-delimited, pipe-delimited, and other delimiter-separated files.

To query a file named products.csv located in the C:\myData directory, use the following SQL statement:

SELECT * 
FROM read_csv('C:\myData\products.csv');

When executed in QuerySurge, the following results are returned:

Delimited File Configuration
When using the read_csv function, the Delimited File driver automatically analyzes the input file and attempts to infer several settings. This automatic detection simplifies querying CSV and other delimited files by minimizing the amount of configuration required from the user.

The driver primarily detects the following file properties:

Delimiter – The character used to separate values within each row (for example, comma, tab, semicolon, or pipe).
Column Data Types – The most appropriate data type for each column, such as INTEGER, DOUBLE, DATE or VARCHAR
Header Row – Whether the first row contains column names or actual data values.

In most cases, these settings are inferred automatically and require no additional input. However, there may be situations where the detected values do not match the structure of the file or where specific behavior is desired. To accommodate these scenarios, the read_csv function allows each of these options to be explicitly specified, overriding the automatically detected settings.

The following sections demonstrate how to customize these properties and control the behavior of the read_csv function when reading delimited files.

Delimiter Value
Using the delim argument we can explicitly set the file delimiter. For example if your files are pipe delimited we can use the below syntax:

SELECT *
FROM read_csv('C:\myData\flights.csv', delim = '|');

Column Data Types
Column data types can be configured by providing the columns argument.

SELECT * 
  FROM read_csv('C:\myData\products.csv',
  columns={'id': 'INT',
  'product_name': 'VARCHAR',
  'price': 'FLOAT',
  'in_stock': 'BOOLEAN'
  });

The driver permits VARCHAR to be used as the default column type for all fields; therefore, the following query is considered valid and will execute without issue.

SELECT * 
  FROM read_csv('C:\myData\products.csv',
  columns={'id': 'VARCHAR',
  'product_name': 'VARCHAR',
  'price': 'VARCHAR',
  'in_stock': 'VARCHAR'
  });

Headers
Boolean flag indicating if the first row is a header or contains actual values. For example consider the file places.csv which is a file which does not contain a header line. If we write the query:

SELECT * FROM read_csv('C:\myData\places.csv');

Following result is produced in QuerySurge:

As seen this is not the desired result, as USA and Washington DC are part of the data and are not column headers.

We can re-write the query supplying the header argument and setting it to false

SELECT * FROM read_csv('C:\myData\places.csv', header=false);

Which produces the following result:

Note that since we did not have a header row in our file and we explicitly set header to false, the driver automatically generated header names. Explicit names can be provided using column aliasing or supplying name parameter.

Example using column aliasing:

SELECT column0 as Country, column1 as Capital 
FROM read_csv('C:\myData\places.csv', header=false);

Example using the name parameter:

SELECT * FROM read_csv('C:\myData\places.csv', header=false, names=['Country','Capital']);

QuerySurge output:

Joins

Joins are supported by the delimited file and can be queried using standard SQL syntax, including joins. In the below example, we are joining data from three separate files customers.csv, addresses.csv, and orders.csv

SELECT
    c.ID,
    c.OID,
    c.FNAME,
    c.LNAME,
    a.STREET,
    a.CITY,
    a.STATE,
    a.ZIP,
    o.ORDERID,
    o.PRICE
FROM read_csv('C:\myData\customers.csv') c
JOIN read_csv('C:\myData\addresses.csv', header = false, delim = '|', 
  names=['ID','OID','STREET','CITY','STATE','ZIP']) a
    ON c.OID = a.OID
JOIN read_csv('C:\myData\orders.csv') o
    ON c.OID = o.OID;

Multiple Files
In some situation data might be split amongst multiple files. The read_csv function provides a simple syntax to join these records by provided a comma separated list as seen below.

SELECT * FROM read_csv(['C:\myData\products1.csv', 'C:\myData\products2.csv']);

If files have differing schemas such as different column names or missing columns use union_by_name option to unify the schema of files. For files that do not have certain columns, NULL values are filled in.

E.g.

SELECT * FROM 
read_csv(['C:\myData\products1.csv', 'C:\myData\products2.csv'], union_by_name = true);

Metadata
Metadata information on Delimited file(s) can be extracted by the driver such as the column names and data types. This information is returned in a tabular format using. The following query retrieves the metadata contained in a Delimited file:

SELECT column_name, column_type FROM
  (DESCRIBE SELECT * FROM read_csv('C:\myData\products.csv'));

When executed in QuerySurge, the following results are returned:

Resources

Articles in this section