Ingest data from Azure Blob Storage without a Hive metastore

[delsehi]

I'm trying to setup Trino with Nessie for creating a data lakehouse with Iceberg tables. I want to ingest data (csv, json, etc) with Trino and load the Iceberg tables (ELT with Trino).

I've played around with a Docker-compose file:

version: '3.9'

services:
  nessie:
   image: "ghcr.io/projectnessie/nessie"
   ports:
     - 19120:19120
  trino:
    image: trinodb/trino
    volumes:
      - ./trino/nessie.properties:/etc/trino/catalog/nessie.properties:ro
    ports:
      - 8080:8080

And I mount a volume with properties:

connector.name=iceberg
iceberg.catalog.type=nessie 
iceberg.nessie-catalog.uri=http://nessie:19120/api/v1
iceberg.nessie-catalog.default-warehouse-dir=/tmp

hive.azure.wasb-storage-account=<The name of my storage account>
hive.azure.wasb-access-key=<My secret key>

I cannot figure out how to query a file though and add it.

I've tried creating schemas like

CREATE SCHEMA nessie.mytest WITH ( location = 'wasb://[email protected]/somepath');
CREATE TABLE nessie.mytest.testing (name varchar);

But I realize this might not work since I'm using Nessie.
I want to avoid setting up a Hive metastore for the only sake of accessing the data before loading it into Iceberg tables + Nessie.
Is there any way of doing that or am I really thinking of Trino in the wrong way?

[delsehi]

Experimented again.

docker-compose.yml

version: '3.9'

services:
  nessie:
   image: "ghcr.io/projectnessie/nessie"
   ports:
     - 19120:19120
  trino:
    image: trinodb/trino
    volumes:
      - ./trino/nessie.properties:/etc/trino/catalog/nessie.properties:ro
    ports:
      - 8080:8080

nessie.properties

connector.name=iceberg
iceberg.catalog.type=nessie
iceberg.nessie-catalog.uri=http://nessie:19120/api/v1
iceberg.nessie-catalog.default-warehouse-dir=/tmp

hive.azure.abfs-storage-account=<Storage account>
hive.azure.abfs-access-key=<Secret key>

I connect to Trino and run:

create schema nessie.tabletest;
use nessie.tabletest;
create table test1(id int) with (location = 'abfs://[email protected]/landing-zone');
insert into test1 (id) values (2);

And this works, I can see the Iceberg tables created in my data lake.
But I want to load external files into my lakehouse. I attempted this:

create table meters (_id varchar, name varchar) with (external_location = 'abfs://[email protected]/metry/landing-zone/meters.json', format = 'json');

Which gives me Catalog 'nessie' table property 'external_location' does not exist

So i try:

create table meters (_id varchar, name varchar) with (location = 'abfs://[email protected]
ows.net/landing-zone.json', format = 'json');

Which returns Unable to set catalog 'nessie' table property 'format' to ['json']: Invalid value [json]. Valid values: [ORC, PARQUET, AVRO]

So I convert that JSON to Parquet and upload it.

create table meters (_id varchar, name varchar) with (location = 'abfs://[email protected]/landing-zone/meters.parquet', format = 'parquet');

With the result --Cannot create a table on a non-empty location: <location>, set 'iceberg.unique-table-location=true' in your Iceberg catalog properties to use unique table locations for every table.

[hashhar]

AFAIK a collection of parquet files != Iceberg table unlike Hive.

For creating Iceberg table from bunch of parquet files you need to:

• Create Hive table
• Use migrate procedure to convert it to Iceberg table

Alternatively add the Parquet file to Iceberg table via table management commands (I know there's addFiles in Iceberg Java API - I don't know if it's exposed in Trino or other query engines)

cc: @findinpath / @alexjo2144 Please correct if something is wrong.

[delsehi]

Thanks for the reply 💞

Yes, I'm aware of that Iceberg tables needs special logfiles and a metastore to function.
What I guess makes me confused is that I tried to follow the instructions on Hive/metastores by setting the metastore to my nessie catalog. I assumed this would still create a Hive Connector allowing me to query Azure Storage.

But I guess that my intended use case is not supported for the time being. Seems like not many tools support writing Iceberg tables without a Hive metastore or Spark (Pyiceberg, Polars, Pandas) and still allowing me to query CSV/JSON (Trino).

[hashhar]

This is more of a Nessie issue though if Nessie can't be used as a metastore for Hive.
If you use a real HMS (like Hive Metastore or Glue) then you can configure two catalogs in Trino - one with the hive connector and one with the iceberg connector.

Then you can do the following sequence of operations:

-- Prepare empty Iceberg table to store the parquet files
CREATE TABLE iceberg.default.landing WITH (location = 'abfs://[email protected]/landing-zone', format = 'PARQUET');

-- Upload one or more JSON files to abfs://[email protected]/landing-zone-json
CREATE TABLE hive.default.landing_json WITH (external_location = 'abfs://[email protected]/landing-zone-json', format = 'JSON');

SELECT * FROM hive.default.landing_json;
-- this should show you all the records from the JSON files

-- Move data from JSON files into Iceberg table
INSERT INTO iceberg.default.landing SELECT * FROM hive.default.landing_json;

SELECT * FROM iceberg.default.landing;
-- This should show same data as the JSON files but it's now reading from the Iceberg table made up of Parquet files

Now if you want to repeat the ingestion procedure you can create the Hive table as a partitioned one and then in the INSERT INTO SELECT query add a filter to only process newly created partitions.

Also another possible solution (this isn't implemented today) is to expose the Iceberg add_files procedure in Trino so that you don't need to create a Hive table first and can instead do something like CALL iceberg.default.add_files('path-to-file', 'table_name') or something.