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
- What you need
- Create the Python environment
- Put your settings in .env.local
- Run the preflight
- Onboard the sources and build the USL
- Confirm the USL has the data
- Check the helper packages
- Probe the object store
- Create the storage setting in Zetaris
- Materialize the USL tables with SQL
- Verify the whole chain
- Explore the data in the viewer
- If something fails
- Record the run and send it back
- 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) orvirtual.S3_REGION: the region name sent to the store,us-east-1by 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.companyreturns 7 (step 6). If you removed the Zetaris objects, rebuild them with step 5; .env.localis 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.