Pinotdb for VS Code

This VS Code extension allows you to navigate and execute SQL statements against Apache Pinot and StarTree cloud. Administrative features will be available in the future.

Features

• Table Navigation - See below for requirements for connecting to your Pinot instance.
• View Table - Click on any table in the tree view to see schema and table details.
• Preview table - Previews the selected table.
• SQL Execution - create a .sql file and enter your SQL. Run shift+command+P and select Pinot: execute SQL.
• Add Schema - Run shift+command+P and select Pinot: Add schema. This will search for files with a .json extension. Select your schema file and hit enter.
• Add Table Config - Run shift+command+P and select Pinot: Add table config. This will search for files with a .json extension. Select your table configuration file and hit enter.
• Delete Table and Schema - In the Pinotdb navigation tree, right-click on the table node and select Delete table and schema. A confirmation will pop up to confirm deletion.
• Run A Job - Runs a job to batch upload data into Pinot via Docker. You will need to install Docker for this feature to work. Create a jobspec.yaml, shift+command+P, and select Pinot: Ingest data into table. See below for details.

Requirements

1. You will need VSCode ^1.91.0.

2. This extension requires a .env file in the root of your project to hold the following security information. If you don't have one, PinotDB will default to localhost. If you add a .env file, clicking on 🔄 will refresh the PinotDB connection, and the extension will load your properties.

PINOT_TOKEN=
PINOT_USER=
PINOT_PASSWORD=
PINOT_WORKSPACE=
PINOT_BROKER=
PINOT_CONTROLLER=

If you don't provide a .env file, the extension will default to unsecured Pinot at localhost:9000.

Creating a JobSpec

Running an ingestion job requires installing Docker. The extension will pull the apachepinot/pinot:1.1.0, generate segments, then load them into Pinot.

For CSV, create a jobspec.yaml file and copy and paste the contents below.

• Replace my_workspace.my_table with your workspace (optional) and table name.
• Replace my.pinot.controller.startree.cloud with your controller URL.

executionFrameworkSpec:
  name: 'standalone'
  segmentGenerationJobRunnerClassName: 'org.apache.pinot.plugin.ingestion.batch.standalone.SegmentGenerationJobRunner'
  segmentTarPushJobRunnerClassName: 'org.apache.pinot.plugin.ingestion.batch.standalone.SegmentTarPushJobRunner'
  segmentUriPushJobRunnerClassName: 'org.apache.pinot.plugin.ingestion.batch.standalone.SegmentUriPushJobRunner'
jobType: SegmentCreationAndTarPush

inputDirURI: '/work/data'
includeFileNamePattern: 'glob:**/*.csv'
outputDirURI: 'segments'
overwriteOutput: true

pinotFSSpecs:
    - scheme: file
      className: org.apache.pinot.spi.filesystem.LocalPinotFS

recordReaderSpec:
  dataFormat: 'csv'
  className: 'org.apache.pinot.plugin.inputformat.csv.CSVRecordReader'
  configClassName: 'org.apache.pinot.plugin.inputformat.csv.CSVRecordReaderConfig'
  configs:
    delimiter: ','

tableSpec:
  tableName: 'my_workspace.mytable'
  schemaURI: 'https://my.pinot.controller.startree:443:443/tables/my_workspace.mytable/schema'
  tableConfigURI: 'https://my.pinot.controller.startree:443/tables/my_workspace.mytable'

pinotClusterSpecs:
    - controllerURI: 'https://my.pinot.controller.startree.cloud:443'

pushJobSpec:
  pushAttempts: 2
  pushRetryIntervalMillis: 1000

Future Features

• View segments
• Set properties
• Batch Upload Data - Parquet
• Data Generator from Pinot schema
• Pinot Schema Generator from CSV, Avro, JSON, DDL
• Generative AI features