Work with pfSQL CLI

Note

This feature is currently in alpha. If you want to try it out or have any questions, submit a ticket to the support team.

pfSQL CLI is a command line tool developed in Rust to perform pfSQL queries, interacting with pfSQL endpoints. This section walks you through the steps to set up pfSQL CLI via Homebrew or Docker.

Prerequisites

To fully use the feature provided by pfSQL, you will need to follow Set up your environment to set up your Pulsar Functions environment first.
Get the pfSQL gateway’s service URL on StreamNative Cloud Console. It shares the same service URL as your Pulsar cluster.
Get your service account's token or OAuth2 credential file, see Get the service account key file or token for more details.

Setup pfSQL CLI via Homebrew

Install pfSQL CLI

brew tap streamnative/streamnative
brew install pfsql-cli

Connect to pfSQL gateway

After installing pfSQL CLI via Homebrew, you can pass the pfSQL connection configs to pfsql either through the environment variables or through the command lines.

Note

The configurations passed through command lines can overwrite those passed through environment variables. In other words, if you pass the configurations through both options, those passed through command lines are used.
If you connect to your pfSQL gateway through command lines, you need to assemble the command for connecting to the pfSQL gateway into each of your pfSQL commands.

Option1: Connect through environment variables

To pass the connection configs through the environment variables, run the following command based on the authentication provider you use.

export PFSQL_BACKEND="${serviceUrl}"
export PFSQL_AUTH_PROVIDER="oauth2"
export PFSQL_AUTH_PARAMETERS="{\"credentials_url\":\"file://$OAUTH2_FILE_PATH\", \"issuer_url\":\"$ISSUER_URL\", \"audience\":\"$AUDIENCE\"}"
pfsql info

PFSQL_BACKEND: the HTTP service URL of your Pulsar Cluster.
PFSQL_AUTH_PROVIDER: use "oauth2" as the authentication type.
PFSQL_AUTH_PARAMETERS:
- credentials_url: the path to your downloaded OAuth2 credential file. It supports the following pattern formats:
  - file://path/to/file
  - data:application/json;base64,<base64-encoded value>
- issuer_url: the URL of your OAuth2 authentication provider. You can get the value from your downloaded OAuth2 credential file.
- audience: the Uniform Resource Name, which is a combination of the urn:sn:pulsar, the organization name, and the Pulsar instance name, in this format urn:sn:pulsar:<org_name>:<instance_name>.

Output of pfsql info would be like:

{
  "status": "RUNNING",
  "runtimes": {
    "pulsar_function": "RUNNING"
  },
  "version": "v0.18.0",
  "whoAmI": "[email protected]"
}

Option2: Connect through CLI options

To pass the connection configs through command line options, run the following command based on the authentication provider you use.

pfsql -b ${serviceUrl} --auth-provider oauth2 --auth-parameters '{"credentials_url":"file://PATH", "issuer_url":"issuer_url", "audience":"urn:sn:pulsar:org:instance"}' info

-b: the HTTP service URL of your Pulsar Cluster.
--auth-provider: use "oauth2" as the authentication type.
--auth-parameters: the parameters for OAuth2 authentication. It supports the following pattern formats:
- credentials_url: the path to your downloaded OAuth2 credential file. It supports the following pattern formats:
  - file://path/to/file
  - data:application/json;base64,<base64-encoded value>
- issuer_url: the URL of your OAuth2 authentication provider. You can get the value from your downloaded OAuth2 credential file.
- audience: the Uniform Resource Name, which is a combination of the urn:sn:pulsar, the organization name, and the Pulsar instance name, in this format urn:sn:pulsar:<org_name>:<instance_name>.

Output of pfsql info would be like:

{
  "status": "RUNNING",
  "runtimes": {
    "pulsar_function": "RUNNING"
  },
  "version": "v0.18.0",
  "whoAmI": "[email protected]"
}

Setup pfSQL CLI via Docker

Install pfSQL CLI via Docker

docker pull docker.cloudsmith.io/streamnative/pfsql/pfsql-cli:0.18.0

Connect to pfSQL gateway

Connect to the pfSQL gateway by passing the connection configs to your docker container. The pfsql execuable is located as /pfsql in the pfsql-cli docker image.

docker run -it –rm -e PFSQL_BACKEND="${serviceUrl}" -e PFSQL_AUTH_PROVIDER=oauth2 -e PFSQL_AUTH_PARAMETERS='{"credentials_url":"file:///tmp/credentials.json", "issuer_url":"issuer_url", "audience":"urn:sn:pulsar:org:instance"}' -v ${CREDENTIL_PATH}:/tmp/credentials.json docker.cloudsmith.io/streamnative/pfsql/pfsql-cli:0.18.0 bash
# after the container running, and you could call `/pfsql` from the container’s terminal
/pfsql info

For more information about the parameters, see Setup pfSQL CLI via Homebrew.

pfSQL CLI Referneces

Usage

pfsql [OPTIONS] -b <BACKEND_URL> <SUBCOMMAND>

Options

The following table outlines the options you can use with pfSQL.

Option	Required	Description
`-b`	Yes	Specify the pfSQL gateway service URL. You can use the value of the `PFSQL_BACKEND` environment variable.
`--auth-provider`	No	Specify the authentication provider. Available values are `jwt` and `oauth2`.
`--auth-parameters`	No	Specify the authentication parameters.
`-v`	No	Display more output per occurrence.
`-q`	No	Display less output per occurrence.
`-h`	No	Print help information.
`-V`	No	Print version information.

Subcommands

The following table outlines the subcommands you can use with pfSQL.

Subcommand	Description
`info`	Get the pfSQL gateway status and version.
`query`	Manage pfSQL queries. You can use it to execute a pfSQL query or to list the available queries. For more details, see Query subcommands
`udf`	Manage pfSQL UDFs.
`health-check`	Check the health status of the pfSQL gateway.

Query subcommands

The following table outlines the subcommands you can use with pfsql query.

Subcommand	Description
`pfsql query metadata`	Get query metadata.
`pfsql query list`	List all queries.
`pfsql query status`	Get query status.
`pfsql query stats`	Get query stats.
`pfsql query run`	Submit a new query.
`pfsql query pause`	Pause a query.
`pfsql query resume`	Resume a query.
`pfsql query delete`	Delete a query.
`pfsql query preview`	Preview a query’s result.

Prerequisites

Setup pfSQL CLI via Homebrew

Install pfSQL CLI

Connect to pfSQL gateway

Option1: Connect through environment variables

Option2: Connect through CLI options

Setup pfSQL CLI via Docker

Install pfSQL CLI via Docker

Connect to pfSQL gateway

pfSQL CLI Referneces

Usage

Options

Subcommands

Query subcommands

What's next