Articles in this section

See more

Writing SQL Queries: Parquet Files

QuerySurge supports reading Parquet files using a JDBC driver that is deployed automatically. Using this driver data testers can use familiar SQL syntax to retrieve data from Parquet 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

Parquet File connection 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 Parquet File, see Configuring Connections: Parquet File

Querying Parquet Files

The Parquet 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_parquet is used to load data from a Parquet file. Below is an example of this syntax where filename is the full file path to the Parquet file being queried. 

SELECT * 
FROM read_parquet(filename);

To read a parquet file named userdata.parquet located in directory C:\myData, we can write the query:

select * from read_parquet('C:\myData\userdata.parquet');

When executed in QuerySurge, the following results are returned:

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

SELECT *
 FROM read_parquet(['file1.parquet', 'file2.parquet']);

Metadata
Metadata information on Parquet 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 parquet file:

SELECT column_name, column_type FROM
  (DESCRIBE SELECT * FROM read_parquet('C:\myData\userdata.parquet'));

When executed in QuerySurge, the following results are returned:

Resources

Related articles