How to Use the Command 'prqlc' (with Examples)

PRQL (Pipelined Relational Query Language) is an innovative language designed to offer a simpler, modern replacement for SQL. The PRQL compiler, prqlc, is a tool that translates PRQL scripts into SQL queries, enabling users to leverage the capabilities of both languages for data transformation tasks. The following guide explores several practical use cases of the prqlc command, highlighting its versatility through real-world examples.

Use Case 1: Run the Compiler Interactively

Code:

prqlc compile

Motivation: Running the compiler interactively allows for on-the-fly PRQL query experimentation and transformation. It’s akin to having a real-time dialogue with your data, providing the flexibility to test and tweak queries instantly, ensuring the results meet your expectations before finalizing them in a script or application.

Explanation:

• prqlc: This is the main command to invoke the PRQL compiler.
• compile: This subcommand tells prqlc to start the compilation process in interactive mode, which allows users to input queries manually and see immediate results or error messages.

Example Output: Upon entering this mode, you would typically see a prompt that invites you to input PRQL queries. After entering a query, the compiled SQL or an error message, if there’s a syntax issue, will appear on the screen.

Use Case 2: Compile a Specific .prql File to stdout

Code:

prqlc compile path/to/file.prql

Motivation: This approach is beneficial when you have a PRQL script saved in a file and you wish to see the SQL conversion directly in the terminal. It simplifies sharing, reviewing, or even debugging the SQL output without altering the file itself.

Explanation:

• prqlc: Initiates the compiler.
• compile: Instructs it to perform the file compilation.
• path/to/file.prql: Indicates the file path of the PRQL script to be compiled. The SQL output will be sent to standard output (stdout), which generally displays in the terminal.

Example Output: You will see the SQL equivalent of the PRQL script printed directly in the terminal window. This facilitates quick checks without needing further applications.

Use Case 3: Compile a .prql File to a .sql File

Code:

prqlc compile path/to/source.prql path/to/target.sql

Motivation: Saving the compiled SQL directly to a file is useful for automation, version control, or integration into systems where the SQL query will be repeatedly utilized or shared with a broader team. Using this method ensures that the SQL output is stored persistently and can be easily accessed later.

Explanation:

• prqlc: Calls the compiler.
• compile: Executes the conversion process.
• path/to/source.prql: Specifies the source PRQL file.
• path/to/target.sql: Defines the target file where the SQL should be saved.

Example Output: In the specified directory, target.sql will be created or overwritten with the SQL code derived from the PRQL input in source.prql.

Use Case 4: Compile a Query

Code:

echo "from employees | filter has_dog | select salary" | prqlc compile

Motivation: Short, direct, and immediate. This technique is perfect for quick transformations of simple query strings. You might use this in scripts or as part of a command-line data query operation that extracts and compiles data transformation tasks concisely.

Explanation:

• echo "..." |: The text within the quotes represents the PRQL command line query. echo pipes this query into the next part of the command.
• prqlc compile: Receives the piped PRQL string and compiles it into SQL.

Example Output: The SQL translation of the given PRQL snippet appears instantly in the terminal, representing the ’employees’ filter and selection logic specified in PRQL.

Use Case 5: Watch a Directory and Compile on File Modification

Code:

prqlc watch path/to/directory

Motivation: This use case shines in development environments that demand frequent file updates and automatic recompilation. It’s akin to implementing a ‘watchdog’ that ensures your SQL code is always in sync with PRQL changes without manual intervention.

Explanation:

• prqlc: Starts the PRQL compilation tool.
• watch: Activates a mode where changes in the specified directory are monitored.
• path/to/directory: Denotes the folder containing PRQL files. When any file changes, prqlc re-compiles it automatically.

Example Output: No output is generated directly. Instead, any time a PRQL file is modified, the corresponding compiled SQL file is automatically updated. This keeps compiled queries fresh and aligned with the latest scripts.

Conclusion:

The prqlc command enriches data querying and transformation processes by making them more intuitive, flexible, and adaptable to various workflow needs. Through these diverse use cases, prqlc empowers data professionals to streamline their operations, reduce manual errors, and ultimately foster a deeper, more efficient interaction with their data.