Getting started
View repository
Getting started / Object store step by stepRead source ↗

HOWTO: build a USL, materialize it to your S3-compatible object store, and prove it

Fifteen steps from an unpacked package to a verified Parquet object in a bucket, ending with the report to send back. Each step has the command to copy and the output to expect. Replace anything in angle brackets with your own value. Timestamps, ids and file names in the output will differ.

Run every command from the folder that holds systems/, scripts/ and open_data/: the package folder, or the repository root.

Commands that start with .venv/bin/python need the venv from step 2. Commands that start with python3 use only the standard library, or switch into the venv themselves when they need it.

Contents

  1. What you need
  2. Create the Python environment
  3. Put your settings in .env.local
  4. Run the preflight
  5. Onboard the sources and build the USL
  6. Confirm the USL has the data
  7. Check the helper packages
  8. Probe the object store
  9. Create the storage setting in Zetaris
  10. Materialize the USL tables with SQL
  11. Verify the whole chain
  12. Explore the data in the viewer
  13. If something fails
  14. Record the run and send it back
  15. Clean up

1. What you need

  • Zetaris Lightning 2.4.3.1 or later, running, with JDBC on port 10000 and the REST API on port 8888, and a user that can create USLs and storage settings.
  • The Zetaris JDBC driver JAR, and a JDK 11 or later (JDK 17 was used).
  • Python 3.10 or later.
  • A Zetaris API key that you create yourself in the Zetaris GUI. The scripts never create one.
  • For your S3-compatible object store: the S3 endpoint URL, an access key, a secret key and an existing bucket.
  • The package, unzipped, or a checkout of the repository.

2. Create the Python environment

python3 -m venv .venv
.venv/bin/pip install -r systems/object_stores/requirements.txt

Expect no errors. The environment gives the scripts jaydebeapi for JDBC, boto3 for the S3 API and pyarrow to read Parquet. The scripts find a Homebrew JDK on macOS by themselves.

3. Put your settings in .env.local

cp systems/object_stores/.env.example .env.local

Open .env.local and fill in every line that does not start with #. It holds the Zetaris login, the API key, the user agent, the JDBC JAR path and the four S3 settings, and the file explains each one. The optional lines show their defaults:

ZETARIS_USERNAME=<your Zetaris user id>
ZETARIS_PASSWORD=<your Zetaris password>
ZETARIS_API_KEY=<API key created in the Zetaris GUI>
ZETARIS_USER_AGENT=<app name> <contact email>
ZETARIS_JDBC_JAR=/full/path/to/ndp-jdbc-driver-<version>-driver.jar

S3_ENDPOINT_URL=https://<your S3 endpoint>
S3_BUCKET=<bucket name>
S3_ACCESS_KEY=<access key>
S3_SECRET_KEY=<secret key>
S3_PREFIX=<your own folder, for example jane-usl-test>

S3_PREFIX is your own folder in the bucket. Everything the test writes goes under it, so people who share one bucket do not collide, and the scripts create the folder for you. Leave it blank to use usl-test- plus your login name.

Three optional settings adapt the scripts to a store that behaves differently. Leave them out until the probe (step 8) tells you to set them:

  • S3_ADDRESSING: path (the default) or virtual.
  • S3_REGION: the region name sent to the store, us-east-1 by default.
  • S3_CA_BUNDLE: a PEM file, for a store whose certificate authority is private.

ZETARIS_USER_AGENT is sent to the public SEC EDGAR API, which asks callers to identify themselves. Use a real contact. The REST URL defaults to http://localhost:8888/api/v1.0 and the organization id to 1, so a local install needs nothing more.

Never paste keys into a chat or a commit, and keep .env.local out of any archive you share.

4. Run the preflight

Read-only. It checks the settings, REST, Java, the venv, the JAR, a JDBC SELECT 1 and, with --s3, the object store settings. It makes no call to the store; the probe in step 8 does that.

python3 scripts/preflight.py --jdbc --s3

Expected output:

[PASS] env file: <folder>/.env.local
[PASS] ZETARIS_API_KEY: set
[PASS] ZETARIS_USERNAME: set
[PASS] ZETARIS_PASSWORD: set
[PASS] ZETARIS_USER_AGENT: set, in the form app_name user@domain.tld
[PASS] REST datasources: HTTP 200, 0 listed: 
[PASS] REST SELECT 1
[INFO] datasources: none
[INFO] lightning databases: <your databases>
[INFO] USL namespaces: none
[PASS] JDBC port 10000: open
[PASS] Java (JDK 11+): /opt/homebrew/opt/openjdk@17/libexec/openjdk.jdk/Contents/Home
[PASS] .venv: <folder>/.venv/bin/python
[PASS] jaydebeapi: importable
[PASS] driver JAR: /full/path/to/ndp-jdbc-driver-<version>-driver.jar
[PASS] JDBC SELECT 1
[INFO] datasources: none
[INFO] lightning databases: <your databases>
[INFO] USL namespaces: none
[PASS] S3_ENDPOINT_URL: set
[PASS] S3_BUCKET: set
[PASS] S3_ACCESS_KEY: set
[PASS] S3_SECRET_KEY: set
[PASS] S3_ADDRESSING: path
[INFO] S3_CA_BUNDLE: not set (the CA file Python trusts is used)
READY

The three INFO lines show what is already on the instance. They appear twice, once after the REST checks and once after the JDBC check, and your lists will differ. If it ends with NOT READY, fix the line marked FAIL first. Every later step assumes READY.

5. Onboard the sources and build the USL

Show the plan first. Nothing is sent.

python3 scripts/onboard.py run sic_edgar_usl --channel jdbc --dry-run

Expected output:

 1. company_dns      rest  open_data/rest_apis/sql/rate_limited/12_company_dns_create.sql
 2. edgar_facts      rest  open_data/rest_apis/sql/rate_limited/01_edgar_company_facts_create.sql
 3. edgar_profiles   rest  open_data/rest_apis/sql/rate_limited/11_edgar_company_profiles_create.sql
 4. sic_edgar_usl    usl   open_data/usl/sql/02_sic_edgar_usl_create.sql
(dry run: nothing sent)

Then run it. This creates objects on the instance and calls the public APIs once, so run it once and deliberately.

python3 scripts/onboard.py run sic_edgar_usl --channel jdbc

Expected output:

=== company_dns (10 statements, dependency)
    10 ok
=== edgar_facts (16 statements, dependency)
    16 ok
=== edgar_profiles (15 statements, dependency)
    15 ok
=== sic_edgar_usl (9 statements)
    9 ok
done

Sources that are only pulled in as dependencies skip "already exists" by themselves, so leftovers from an earlier run do no harm. Naming a source or USL that already exists needs --skip-exists, otherwise the run stops at the first duplicate. The line SLF4J: Failed to load class is a harmless warning from the driver, which bundles no logging backend.

6. Confirm the USL has the data

python3 scripts/run_sql.py --channel jdbc -e "SELECT COUNT(*) FROM lightning.metastore.usl_demo.sic_edgar_usl.sic_code"
python3 scripts/run_sql.py --channel jdbc -e "SELECT COUNT(*) FROM lightning.metastore.usl_demo.sic_edgar_usl.company"
python3 scripts/run_sql.py --channel jdbc -e "RUN DQ TABLE lightning.metastore.usl_demo.sic_edgar_usl.company"

Expected output, one line from each command:

[1005]
[7]
['cik', 'company', 'Primary Key Constraint', 7, 7, 0]
['sic', 'company', 'Foreign Key Constraint', 7, 6, 1]

One foreign-key row is invalid on purpose in this data set: IBM is filed under SIC 3570, which is not in the SIC hierarchy. It does not stop materialization.

7. Check the helper packages

.venv/bin/python -c "import jaydebeapi, boto3, pyarrow; print('helpers ok')"

Expected output: helpers ok

8. Probe the object store

This tests the endpoint, the keys and the bucket before Zetaris is involved. It tries your configured addressing style and region first, then the other style and three region names, then writes one tiny test object under <your folder>/zetaris-probe/ and deletes it. Add --no-write to stay read-only.

.venv/bin/python systems/object_stores/scripts/probe_s3.py

Expected output:

endpoint=https://<endpoint> bucket=<bucket> tls_verify=<CA file>
configured: addressing=path region=us-east-1
your folder: s3://<bucket>/<your folder>/
[PASS] style=path    region=us-east-1  head_bucket + list ok (0 keys shown)
[PASS] style=path    region=us-west-2  head_bucket + list ok (0 keys shown)
[PASS] style=path    region=eu-west-1  head_bucket + list ok (0 keys shown)
[PASS] style=virtual region=us-east-1  head_bucket + list ok (0 keys shown)
[PASS] style=virtual region=us-west-2  head_bucket + list ok (0 keys shown)
[PASS] style=virtual region=eu-west-1  head_bucket + list ok (0 keys shown)

Your configured settings work: S3_ADDRESSING=path S3_REGION=us-east-1

Write test with style=path region=us-east-1: <your folder>/zetaris-probe/<uuid>.txt
[PASS] put_object
[PASS] get_object matches
[PASS] delete_object
[PASS] your folder s3://<bucket>/<your folder>/ created

Your store may pass fewer lines. What matters is the line after the table. If it says Your configured settings did not work, but this combination did, put the S3_ADDRESSING and S3_REGION it prints in .env.local and run the probe again. The same two settings drive every later step, including the options Zetaris receives (useS3PathStyleAccess).

The probe writes its test object inside your folder and also creates the folder. S3 has no real directories: a folder is a key prefix, and the script creates the empty marker object that consoles show as a folder. Once data is written under it, the folder shows through its objects, and the storage engine normally removes the empty marker. That is expected.

The probe runs on your machine, so it does not prove that the Zetaris engine can reach the store. If the engine runs on another host, that host must reach the endpoint and trust the same certificate chain. Step 10 is the real test of that.

If every line fails with CERTIFICATE_VERIFY_FAILED: the endpoint's certificate chain may be public, but boto3's bundled CA list may not include its root. The scripts already use the CA file your Python trusts. If the endpoint uses a private CA, set S3_CA_BUNDLE in .env.local (or pass --ca-bundle /path/to/ca.pem). Do not turn verification off.

9. Create the storage setting in Zetaris

There is no SQL statement to create a storage setting, so this calls the REST endpoint POST /storage-setting. Look at the request first, with the keys hidden:

.venv/bin/python systems/object_stores/scripts/storage_setting.py create --dry-run

Expected output:

POST /storage-setting (dry run, nothing sent)
{
  "storageName": "s3-compat-<your folder>",
  "storageType": "S3",
  "storagePath": "s3a://<bucket>/<your folder>",
  "scope": "Owner",
  "config": {
    "awsAccessKeyId": "<redacted>",
    "awsSecretAccessKey": "<redacted>",
    "s3Endpoint": "https://<endpoint>",
    "useS3PathStyleAccess": "true"
  }
}

With S3_ADDRESSING=virtual the useS3PathStyleAccess line is left out.

Create it, then note the id it returns:

.venv/bin/python systems/object_stores/scripts/storage_setting.py create
.venv/bin/python systems/object_stores/scripts/storage_setting.py list

Expected output:

HTTP 200
{
  "id": "<storage setting id>"
}

Creating a setting does not test it. It is accepted even when the endpoint or keys are wrong. The real test is step 10. Use storage_setting.py delete <id> to remove one.

The setting is named s3-compat- plus your folder, so each person has their own. If you get HTTP 400 and already exists, a setting with that name is already on the instance; pass another with --name.

10. Materialize the USL tables with SQL

The script builds the statement from .env.local. Dry-run first. It masks the credentials in what it prints.

.venv/bin/python systems/object_stores/scripts/materialize_usl_table.py \
  lightning.metastore.usl_demo.sic_edgar_usl.sic_code \
  --setting-id <storage setting id> --variant options-json --dry-run

Expected output:

MATERIALIZE USL TABLE lightning.metastore.usl_demo.sic_edgar_usl.sic_code TO STORAGE (
  recordType = 'all',
  path = 's3a://<bucket>/<your folder>/usl_demo/sic_edgar_usl/sic_code',
  format = 'parquet',
  storageSettingId = '<storage setting id>',
  optionsBase64 = '<base64 JSON with credentials>'
)

(dry run: nothing sent)

Run it for both tables:

.venv/bin/python systems/object_stores/scripts/materialize_usl_table.py \
  lightning.metastore.usl_demo.sic_edgar_usl.sic_code \
  --setting-id <storage setting id> --variant options-json

.venv/bin/python systems/object_stores/scripts/materialize_usl_table.py \
  lightning.metastore.usl_demo.sic_edgar_usl.company \
  --setting-id <storage setting id> --variant options-json

The last line of each run:

RESULT: s3a://<bucket>/<your folder>/usl_demo/sic_edgar_usl/sic_code | SUCCESS | None | None
RESULT: s3a://<bucket>/<your folder>/usl_demo/sic_edgar_usl/company | SUCCESS | None | None

The credentials must be inside the options. The manual's example passes only storageSettingId. On Zetaris 2.4.3.1 that failed with NoAuthWithAWSException: No AWS Credentials provided, and so did extra top-level keys and fs.s3a.* names. It worked once the S3 keys, endpoint and useS3PathStyleAccess went into the optionsBase64 JSON, which is what --variant options-json does. The statement therefore carries your keys, and the instance may keep it in query history, so use a key you can rotate. The details are in ISSUE-01.

Everything lands in your own folder, so other people's output in the same bucket is not affected and does not affect you. Running this step again replaces each table's output; it does not append (tested on Zetaris 2.4.3.1 with both tables: the object count and the row count were the same after a second run). Clear your folder (step 14) when you want to remove files from earlier runs that this run no longer writes, so that step 11 proves this run and nothing else.

The company table produces 7 Parquet files of one row each, because it is a UNION ALL of seven sources. That is harmless at this size.

11. Verify the whole chain

This lists your folder in the bucket, then checks both tables from the source API through the view and the USL to the Parquet files. It is read-only and exits with 0 only when every stage matches.

.venv/bin/python systems/object_stores/scripts/verify_chain.py

Expected output (stage lines):

=== sic_code: company-dns API > view > USL > Parquet
1a company-dns API V3 (live)                      1,005  PASS  reference count
1b company-dns API V4 (live)                      1,005  PASS
3  SchemaStore view sic_codes_table               1,005  PASS
4  USL table sic_code                             1,005  PASS
5  Parquet  <your folder>/usl_demo/sic_edgar_usl/sic_code  1,005  PASS
   USL rows vs Parquet rows (8 columns)                  PASS  identical

=== company: SEC EDGAR API > raw tables > view > USL > Parquet
1  SEC EDGAR submissions API (live)                   7  PASS
3  SchemaStore view all_companies_profile_table       6  PASS  omits 1 company with SIC not in sic_code
4  USL table company                                  7  PASS
5  Parquet  <your folder>/usl_demo/sic_edgar_usl/company  7  PASS
   USL rows vs Parquet rows (4 columns)                  PASS  identical

CHAIN OK

Use --table sic_code or --table company to check one table. A FAIL line names the stage and shows a few keys that differ, and the last line then reads CHAIN BROKEN. The view has 6 rows, not 7, on purpose: it omits IBM, whose SIC code is not in the hierarchy.

12. Explore the data in the viewer

Start the small local server. It serves the viewer and runs read-only SQL against your instance with the credentials in .env.local, which never reach the browser.

.venv/bin/python systems/object_stores/scripts/serve_viewer.py

Expected output:

Connected over JDBC (settings from <folder>/.env.local).
Mode: read-only.  Viewer: http://127.0.0.1:8765   (Ctrl+C to stop)

Open http://127.0.0.1:8765 and choose the Live SQL tab. Pick an example or type your own and press Run query. For example:

SELECT cik, entity_name, sic, sic_description_edgar
FROM lightning.metastore.usl_demo.sic_edgar_usl.company
ORDER BY entity_name

Options: --channel rest uses the REST API instead of JDBC, --port 9000 changes the port, and --allow-write lets statements that change the instance through, so leave it off unless you mean it.

Queries on the REST-backed tables can take several seconds, because those tables fetch from the public API again unless they are cached.

13. If something fails

You see Cause and fix
CERTIFICATE_VERIFY_FAILED from boto3 The CA list boto3 bundles lacks your endpoint's root. The scripts use Python's CA file by default. For a private CA set S3_CA_BUNDLE. Never disable verification.
S3_ADDRESSING must be one of path, virtual Fix the value in .env.local.
S3_CA_BUNDLE is set but ... is not a file Fix the path in .env.local, or remove the line.
Probe: Your configured settings did not work, but this combination did Copy the S3_ADDRESSING and S3_REGION it prints into .env.local.
Probe passes but materialize fails The probe runs on your machine and Zetaris runs elsewhere. Check that the engine host can reach the endpoint and trusts its certificate chain.
NoAuthWithAWSException: No AWS Credentials provided in the materialize result The credentials were not inside optionsBase64. Use --variant options-json. See ISSUE-01.
The materialize result says Error although the command printed ok The statement returns one row per run. Read that row: SUCCESS, or Error followed by the message.
boto3 is not installed in this Python, or No module named pyarrow You used python3. Use .venv/bin/python.
Set ZETARIS_USER_AGENT Add an app name and a contact email to .env.local.
Missing in the environment or .env.local: S3_... Add the missing name to .env.local.
Unable to locate a Java Runtime macOS stub. Install a JDK, for example brew install openjdk@17; the scripts find it.
REST 401 from storage_setting.py The API key is missing, wrong or expired. Create a new key in the Zetaris GUI and update .env.local.
The Live SQL tab says Not connected The viewer server is not running, or you opened the file directly. Run .venv/bin/python systems/object_stores/scripts/serve_viewer.py and use the address it prints.
SLF4J: Failed to load class StaticLoggerBinder Harmless. The driver bundles no logging backend. verify_chain.py and serve_viewer.py hide it.

14. Record the run and send it back

This is the step that produces the result to send back: one command runs the test again from start to finish and writes a report. Run it after steps 1 to 6 (and you do not need to have run steps 8 to 11 first, though it does no harm if you did).

Before you run it, check that:

  • the preflight in step 4 passes, including --s3;
  • the USL from step 5 is on the instance. SELECT COUNT(*) FROM lightning.metastore.usl_demo.sic_edgar_usl.company returns 7 (step 6). If you removed the Zetaris objects, rebuild them with step 5;
  • .env.local is complete and you are in the package folder, using the venv from step 2.

Run it:

.venv/bin/python systems/object_stores/scripts/report.py --cleanup

It takes about a minute. It repeats the probe (step 8), creates a storage setting and removes it again (step 9), materializes sic_code and company (step 10), reads the Parquet files back and compares the row counts from the source API to the Parquet file (step 11), and then clears your own folder in the bucket (the bucket part of step 15). Without --cleanup that last part is a dry run and the Parquet files stay in the bucket. It touches only your own folder. Add --store-only to test only the object store, before Zetaris is involved.

It prints the report and writes reports/<store>-<date>-<time>.json and a .md next to it. Each check is PASS, FAIL, SKIP (not run, with the reason) or INFO (a fact, not a verdict). The last lines look like this:

Wrote reports/<store>-<date>-<time>.json
Wrote reports/<store>-<date>-<time>.md
Result: PASS
Result Meaning What to do
PASS (exit code 0) Every required check passed. Send the two files.
FAIL (1) A required check failed, for example the USL is not on the instance, and the checks that depend on it were skipped with the reason. Read the first FAIL line, fix it (step 13), run it again. A FAIL is also worth sending: it shows where the store differs.
INCOMPLETE (3) Nothing failed, but a required check did not run, for example because you used --store-only. Read the SKIP lines; if they are not what you intended, fix that and run it again.
exit code 2 Not written: the report still held a value from .env.local after redaction. Do not work around it. Send the error text, not the report.

The report holds no keys, passwords, bucket name, endpoint, folder name, user agent or login name. They are replaced with placeholders such as <bucket>, and the report is not written at all if one is still found. Send the .json and the .md from reports/.

To read the report formatted, start the viewer (step 12) and open the Proof and recipe tab: the server shows the newest reports/*.json under "Your conformance report" by itself, and again when you return to the page after a new run. It checks the file against your .env.local values again before sending it. Without the server, choose the .json file there; it is read in your browser and goes nowhere.

15. Clean up

Confirm what you want gone first. These steps change the instance and the bucket.

If you are validating the store, do step 14 first and keep its report: it needs the USL, and removing the Zetaris objects below removes the USL. If you cleaned up already, rebuild it with step 5 before step 14.

Zetaris objects. Show the plan, read it, then run it with --yes:

python3 scripts/onboard.py teardown sic_edgar_usl company_dns edgar_facts edgar_profiles --channel jdbc
python3 scripts/onboard.py teardown sic_edgar_usl company_dns edgar_facts edgar_profiles --channel jdbc --yes

This drops the USL, the views and the other SQL-removable objects. The REST tables, the COMPANY_DNS and SEC_DATA Lightning databases and the containers can only be removed in the Zetaris Data Explorer, and the command lists them. They do no harm if you onboard again, because dependencies skip "already exists". --drop-namespace also removes every other USL in usl_demo, so leave it off unless you want that.

Storage setting:

.venv/bin/python systems/object_stores/scripts/storage_setting.py list
.venv/bin/python systems/object_stores/scripts/storage_setting.py delete <storage setting id>

Bucket. The test wrote everything inside your own folder: the Parquet under <your folder>/usl_demo/sic_edgar_usl/ and the probe's test object. The cleanup script lists what it would delete and deletes nothing until you add --yes:

.venv/bin/python systems/object_stores/scripts/cleanup_bucket.py

Expected output, with your own bucket, folder and objects:

Target: bucket <bucket> at <endpoint host>
Mode:   dry run (nothing will be deleted)

<your folder>/  10 objects, 52,699 bytes
    ...
Total: 10 objects, 52,699 bytes under 1 prefix(es)

Dry run: nothing was deleted. Re-run with --yes to delete the objects listed above.

Read the list, then delete:

.venv/bin/python systems/object_stores/scripts/cleanup_bucket.py --yes

Expected output ends with:

Deleted. Checking again:
  <your folder>/  0 objects left

CLEAN: every listed prefix is empty.

By default it touches only your own folder. Output written elsewhere in the bucket is left alone unless you name it, for example --prefix older-output/. Each prefix is treated as a folder, and an empty prefix is refused, so it can never empty the whole bucket. If it ends with INCOMPLETE, objects remain; on a versioned bucket, old versions or delete markers may need your store's console. A bucket with object lock or a retention policy will refuse the deletes, and no script works around that.

Built from the repository documentation. Use your own Zetaris instance, installed locally or deployed through AWS.