A fifty-megabyte CSV from a vendor is sitting in a bucket, or about to be. An agent will search it and parse it. That works until you need to join it to warehouse.public.stores, at which point you are either loading fifty thousand rows through INSERT or pointing Apache Hive at the file where it already lives. I added that second path to mcp-data-platform. This post is the integration, as it sits in internal/platform/tableregister and Trino’s Hive connector.
This is the seventh post in MCP by Design. The earlier notes covered Go, composition, steering, knowledge, testing, and Starlark. This one is the table. It is grounded in the open-source
txn2/mcp-data-platform, also available hosted as Plexara.
§The File Is Already There
CSV is still how a lot of business data moves, because Excel still exports it that way, and so does every vendor who would rather email a dump than open an API. An agent can open a file like that and answer a question about the rows. The gap is the join. The keys in that file mean something against tables that already live in the warehouse, and the warehouse does not know the file exists.
Trino has no client-side upload. INSERT ... VALUES batches for a fifty-thousand-row CSV burn context and time, and they copy the data into a place it did not need to go. A few hundred keys do not need a table at all; they join inline with JOIN (VALUES ('a'), ('b')) AS t(id) through trino_query on a read-only connection. Registration is for the file that is too big for that.
JSON from an API is the same job with an extra step. Flatten the payload with the Starlark engine from the last post, write a CSV the platform already knows how to store, and Hive can query the object. You do not ingest it into the warehouse to join it.
This piece does not convert a Hadoop estate. Iceberg remains the warehouse; see the lakehouse post. Hive, here, is the scratch catalog that makes a CSV in SeaweedFS queryable without copying it.
§Hive, Minus Hadoop
Apache Hive is a catalog that maps a table name to a directory of files and applies a schema when you query. Facebook built it so analysts could write SQL over Hadoop without writing MapReduce. The Hadoop cluster is gone from this platform. The catalog move is not.
Schema-on-read is the whole trick. The files sit wherever they sit. The table is a pointer and a list of columns. Query time is when the engine opens the objects and parses them. The idea is older than the word lakehouse. It is why a spreadsheet export can join a warehouse table without becoming one.
In 2020 I wrote about Hive and Presto. Presto became Trino, the warehouse tables became Iceberg, and the Hive Metastore on its own MySQL went away, replaced by Iceberg’s JDBC catalog in the Postgres the platform already runs. I would not put the warehouse back on Hive, and I am not throwing away a connector that already reads CSV from S3.
Trino still ships that connector. The alternatives for this job are real: Snowflake stages and COPY INTO, BigQuery external tables, DuckDB read_csv on a laptop, Spark’s CSV datasource. This platform already runs Trino against SeaweedFS. Adding a Hive catalog with a file metastore is a properties file, not a new service.
flowchart TB
subgraph warehouse["warehouse: Iceberg"]
P["Parquet in the lake bucket"]
C["JDBC catalog in Postgres"]
end
subgraph scratch["scratch: Hive"]
F["CSV in portal-assets / managed-resources"]
M["file metastore in its own bucket"]
end
T["Trino"] --> warehouse
T --> scratch
T --> J["SELECT ... JOIN"]
A file metastore is enough. No Thrift service, no Hadoop NameNode. Trino writes a few hundred bytes of table metadata into an S3 prefix and reads CSV from the directory the CREATE TABLE named.
§The Scratch Catalog
Registration is available on a Trino connection that names a scratch target: the catalog and schema registrations are written into. Verified against trinodb/trino:476.
# etc/catalog/scratch.properties
connector.name=hive
hive.metastore=file
hive.metastore.catalog.dir=s3://acme-scratch/trino-metastore/
hive.recursive-directories=false
fs.native-s3.enabled=true
s3.endpoint=http://seaweedfs:8333
s3.path-style-access=true
s3.region=us-east-1
s3.aws-access-key=${ENV:SCRATCH_S3_KEY}
s3.aws-secret-key=${ENV:SCRATCH_S3_SECRET}
The metastore bucket holds table metadata only. The CSV bytes stay in portal-assets or managed-resources, the buckets a managed resource upload and a portal asset already use. Trino gets its own SeaweedFS identity for this, scoped to read those two buckets and to own the metastore bucket. It is not the platform’s S3 credentials.
hive.recursive-directories=false matters. Hive reads every non-hidden object under an external location and parses it as CSV. A stray file beside the content does not fail the query; it comes back as rows of that file’s bytes. Each registrable object on this platform already lives under a directory of its own, resources/<id>/file.csv or artifacts/<user>/<id>/content.csv, which is what makes an external table over that directory a table over that file and nothing else.
On the platform side, the connection that runs the DDL is not the connection that runs the join:
toolkits:
trino:
instances:
warehouse:
host: q-demo.plexara.io
user: "${TRINO_READONLY_USER}"
catalog: warehouse
read_only: true
scratch:
host: q-demo.plexara.io
user: "${TRINO_SCRATCH_USER}"
catalog: scratch
schema: uploads
read_only: false
scratch:
catalog: scratch
schema: uploads
After that catalog exists, Trino answers scratch.uploads at the same coordinator the warehouse already uses. manage_table and the portal call the same registrar.
§CREATE TABLE Is the Feature
Nothing is copied. Registrar.Register in tableregister plans the table, refuses on anything it cannot establish, and only then runs DDL. The comment on Register is the order:
// Register makes the source's directory readable as a table and records it.
//
// The order is deliberate: everything that can refuse does so before any
// statement runs, so a refused registration leaves nothing behind in Trino.
// The record is written last, because a row naming a table that was never
// created is worse than a table with no row -- the first is a lie a search hit
// repeats, the second is an object in a scratch schema.
The statements themselves are BuildDDL. CREATE SCHEMA IF NOT EXISTS is first, because the first registration on a connection has to make the target. DROP TABLE is issued only when replacing a registration the caller is entitled to replace. Then the table:
func createTableStatement(r Registration) string {
cols := make([]string, 0, len(r.Columns))
for _, c := range r.Columns {
cols = append(cols, QuoteIdentifier(c.Name)+" "+c.Type)
}
var b strings.Builder
b.WriteString("CREATE TABLE ")
b.WriteString(qualified(r))
b.WriteString(" (")
b.WriteString(strings.Join(cols, ", "))
b.WriteString(") WITH (external_location = ")
b.WriteString(QuoteLiteral(r.Location))
b.WriteString(", format = 'CSV', skip_header_line_count = 1)")
return b.String()
}
external_location is s3://<bucket>/<directory>/. skip_header_line_count = 1 keeps the header out of the rows. Every identifier, including column names taken from a file somebody uploaded, goes through QuoteIdentifier, because Trino has no parameter binding for identifiers and a column called rebate%" OR 1=1 is a name, not a clause.
Dropping a Hive external table removes the catalog entry and leaves the objects. Unregistering never touches the file. Deleting the file drops every table registered over it, because a table over where it used to be would return nothing and explain nothing.
A registration reads the whole object, not a range. Neither S3 adapter has a range read, so learning the first line costs a GetObject, and the CSV inspection below needs the rest of the bytes anyway. DefaultMaxBytes is 100 MiB, matching the managed-resource upload cap. A fifty-megabyte export is inside that. A file larger than the bound is refused rather than half-read.
§VARCHAR, and the CAST
Every column of a registered table is VARCHAR. That is the Hive CSV storage format’s rule, not a platform choice. Declaring the table any other way is refused by Trino itself: “Hive CSV storage format only supports VARCHAR (unbounded).” A join to a typed warehouse column therefore needs a cast, and the tool response carries a sample so the agent does not have to discover the type error:
SELECT s.store_id, s.store_name, u.rebate_pct
FROM warehouse.public.stores s
JOIN scratch.uploads.analyst_vendor_keys u
ON s.store_id = CAST(u.store_id AS integer)
Blank header fields become column_1, duplicate names are suffixed, and a UTF-8 BOM on the first field is stripped so it does not become part of the first column’s name. The file is what it is; the columns still have to be addressable.
§The Directory Is the Table
A registration points at a directory, not at a record id. Two ways of changing the file therefore do opposite things, and mixing them up is how this feature gets used wrong.
| What happened | What the table does |
|---|---|
| The object is overwritten at the same key. A vendor drop replacing yesterday’s file. | The next query returns the new contents. Nothing to do. |
| A new version is written. Every portal asset edit does this: new content, new directory, head moves. | The table keeps serving the directory it was registered against. Correct SQL over the version that was current then. Register again, same connection and name, to move it forward. |
The second case is reported as stale everywhere a registration is shown. A stale table is not dropped. A report built on it keeps running; it is behind, and the platform will not decide on its own that you wanted it moved forward.
A managed script that rewrites a portal asset on a schedule produces a new version per run, so a table over that asset is stale from the first refresh onward until somebody registers it again. A script that overwrites the same object key has no such problem, and is the better shape when the table has to stay current by itself.
Hive skips any name beginning with . or _. Confirmed on Trino 476. Portal thumbnails are written under those names so they can sit beside the CSV without becoming rows. A source object under a hidden name of its own is refused, because a table over it would be created, recorded, and queried without error and return nothing.
§Inspect Before Hive Does
Trino’s Hive CSV reader is line-based. The text input format splits records on \n before the quote-aware serde sees them. A line break inside a quoted cell, an address or a note, tears one record into several. The first fragment ends on an unbalanced quote, and every field after it lands in the wrong column.
The table is created and the query returns rows, so nothing downstream can tell that the file was read wrongly. That is why InspectCSV runs before any statement does, over the bytes contentFor already fetched for the header.
The same reader splits on the newline and on nothing else, so a file whose lines end in a bare carriage return, the classic Mac ending some spreadsheet exports still write, is one record. A table over that file has a single row holding the whole file, and it too is created and queried without error.
Registration refuses a file whose lines end in a carriage return, a line break inside a cell, or bytes that are not UTF-8. A NUL byte is refused on the same ground even where the rest of the file is valid UTF-8, because a UTF-16 “Unicode Text” export of ASCII is valid UTF-8 with a NUL beside every character. None of those refusals are silent.
The refusal offers a correction. repair=true on the tool, or the control in the portal, rewrites the file as a new version of itself: UTF-8 with newline-delimited records, line breaks inside cells folded to spaces. The bytes that were uploaded stay as the version before it. The table is then built over the new version’s directory. A file the correction cannot put right, ragged field counts or a parse that does not finish, is refused once, and no correction is offered for it, because filling in a short record invents data.
A file that is already line-safe UTF-8 registers as it is. No version is written.
§The Boundary Is the Trino User
The scratch schema is a shared workspace. Everyone granted the connection can read every table in it. Resource and asset permissions are not carried into Trino. Registering is therefore authority to change the file, not authority to read it: publishing a persona-scoped CSV into a schema everyone on that connection can query would otherwise be a read that widens the audience.
Table names are prefixed with the registering person’s persona. That is collision avoidance and legibility, not a boundary. Two analysts registering vendors still land on the same name. The unique index on connection, catalog, schema, and table is what decides who holds it. Re-registering a name you hold replaces the table. A name somebody else holds is refused, and the refusal names who. Administrators are unrestricted.
What keeps a registration off the warehouse is the Trino identity the scratch connection authenticates as. The platform’s read_only flag is a statement-prefix denylist evaluated per connection. catalog and schema on a connection are session defaults, not bounds. A scratch connection that authenticates as the same Trino user as the warehouse connection can write to the warehouse.
Give it its own user. On the ACME demo that is mcp-scratch, with access-control rules allowing DDL only on scratch, and mcp-server (the query identity) read-only on that catalog so the join works:
{ "user": "mcp-scratch", "catalog": "scratch", "allow": "all" },
{ "user": "mcp-scratch", "catalog": "system", "allow": "read-only" },
{ "user": "mcp-server", "catalog": "scratch", "allow": "read-only" }
mcp-scratch gets no rule for the warehouse, so it is denied it.
Note: a rule file Trino never loads is the same as no rule. Prove a denial. trino_query on the scratch connection against warehouse.public.stores has to come back Access Denied: Cannot access catalog warehouse. If it does not, the identity boundary is theater.
Exec is the platform’s one write path into Trino. It runs the same ReadOnlyInterceptor the MCP tools run, against the same per-connection read_only settings, so a read-only connection refuses DDL here exactly as trino_execute would. The registrar asks AcceptsWrites before it offers the connection, because a form that offers a connection the DDL then refuses is the same defect as a registration refusing a connection the form offered.
Every registration and unregistration writes an audit event: who, which connection, the statement that ran, and the table it named. A correction that rewrote the file is recorded on the same event, even when the registration that asked for it then failed, because the file changed either way.
§JSON Is a Script, Then a Table
The CSV is the common case. The other one is an API that returns JSON, which an agent can parse and which a warehouse cannot join. The last post embedded Starlark so a job like this survives the conversation. Against whatever endpoint you actually have:
result = platform.call("api_invoke_endpoint", {
"connection": "vendor",
"operation_id": "listRebates",
"purpose": "Refreshing vendor rebate keys for the warehouse join.",
})
status = result.get("status")
if status != 200:
fail("vendor API returned %s" % status)
body = result.get("body")
rows = [
{"store_id": r["id"], "rebate_pct": r["rebate"]}
for r in body["rebates"]
]
out = platform.export(name="vendor_rebates", rows=rows, format="csv")
asset_id = out.get("asset_id")
if not asset_id:
fail("draft run; nothing to register")
platform.call("manage_table", {
"action": "register",
"reference": "mcp:asset:" + asset_id,
"connection": "scratch",
"table_name": "vendor_rebates",
})
platform.call is the same Caller the query binding uses. The script cannot register on a connection the author could not, and it cannot change a file the author could not change. Dates come from the payload or from run.fire_time. A non-200 is fail(...), which fails the run and records the reason. A draft run has no Exporter, so there is no asset_id and nothing to point Hive at; the scheduled body is the one that writes the file.
If this script fires on a schedule and writes a new portal version each time, the table it registered last Monday is stale by Tuesday. Registering again on the same name is the repair, and the snippet above does that every run. Overwriting a stable object key instead is the shape that needs no re-register.
The agent that wrote the script is out of the loop. The people reading the join never see the API.
§What You Take
Hive maps a table name to a directory of files and applies a schema at query time. Trino still ships that connector, and a file metastore on SeaweedFS is enough to use it. The Hadoop cluster is not required. The statement is CREATE TABLE ... WITH (external_location = 's3://...', format = 'CSV', skip_header_line_count = 1). Nothing is copied. Every column is VARCHAR, so a join to a typed warehouse column casts.
The directory is the table, which is why an overwrite at the same key updates it and a new version does not. The Hive CSV reader splits on newline before it understands quotes, so the platform inspects the file first. The Trino identity is what keeps registration DDL off the warehouse. JSON arrives the same way, after a Starlark script has written a CSV.
That is internal/platform/tableregister and etc/catalog/scratch.properties.