EimerDB is a python package that gives database-like functionality to parquet files stored in google cloud storage. It achieves this by organising the parquet files in a certain way, reads and combines them with pyarrow and then query the combined pyarrow tables with duckdb. For use as a part of the statistical production process at Statistics Norway.
Create a new database by specifying the bucket name and a database name.
import eimerdb as db
db.create_eimerdb(bucket="bucket-name", db_name="prodcombasen")
Connect to your EimerDB database.
prodcombasen = db.EimerDBInstance("bucket-name", "prodcombasen")
You can create a new table with the create_table method. Specify the table name, the schema, the partition columns and set if the table is editable or not. Define the columns in the schema, with a column name, type and a label.
schema = [
{
"name": "aar",
"type": "int16",
"label": "Årgangen."
},
{
"name": "ident",
"type": "string",
"label": "Foretakets identifikator."
},
{
"name": "skjemaversjon",
"type": "string",
"label": "Skjemaets versjon."
},
{
"name": "råvarekode",
"type": "string",
"label": "Prefillet råvarekode. Disse kodene lages av NR."
},
{
"name": "beskrivelse",
"type": "string",
"label": "Prefillet råvarebeskrivelse. Disse beskrivelsene lages av NR."
},
{
"name": "forbruk",
"type": "int64",
"label": "Oppgitt forbruk (i 1 000 NOK) til den tilhørende råvarekoden."
},
]
prodcombasen.create_table(
table_name="prefill_prod",
schema,
partition_columns=["aar"],
editable=True
)
Partitioning the table by one or more columns will help improve query performance
Query your tables with SQL syntax. You can optionally specify the partition to be queried.
prodcombasen.query(
"""SELECT *
FROM prodcom_prefill
WHERE produktkode = '10.13.11.20'""",
partition_select = {
"aar": [2022, 2021]
}
Perform updates using SQL statements Each update is saved as a separate parquet file for versioning. The update files includes a username column and a datetime column for when the update happened.
prodcombasen.query(
"""UPDATE prodcom_prefill
SET mengde = 123
WHERE ident = '123456'
AND produktkode = '10.13.11.20'""",
partition_select = partitions
)
Retrieve the unedited version of your data by specifying unedited=True.
prodcombasen.query(
"""SELECT *
FROM prodcom_prefill""",
unedited=True
)
You can query alle the changes made to the table with the query_changes method.
prodcombasen.query_changes(
"""SELECT *
FROM prodcom_prefill""",
unedited=True
)
Query multiple tables using JOIN and subquery.
prodcombasen.query(
f"""SELECT
t1.aar,
t1.produktkode,
t1.beskrivelse,
SUM(t1.mengde) AS mengde
FROM
prefill_prod AS t1
JOIN (
SELECT
t2.aar,
t2.ident,
t2.skjemaversjon,
MAX(t2.dato_mottatt) AS newest_dato_mottatt
FROM
skjemainfo AS t2
GROUP BY
t2.aar,
t2.ident,
t2.skjemaversjon
) AS subquery ON
t1.aar = subquery.aar
AND t1.ident = subquery.ident
AND t1.skjemaversjon = subquery.skjemaversjon
WHERE
t1.mengde IS NOT NULL
GROUP BY
t1.aar,
t1.produktkode,
t1.beskrivelse;""",
partition_select={
"aar": [2022, 2021, 2020]
},
)
Add and remove users from your instance. Assign specific roles to users for access control.
prodcombasen.add_user(username="newuser", role="admin")
prodcombasen.remove_user(username="olduser")
- TODO
You can install EimerDB via pip from PyPI:
pip install ssb-eimerdb
Please see the Reference Guide for details.
Contributions are very welcome. To learn more, see the Contributor Guide.
Distributed under the terms of the MIT license, EimerDB is free and open source software.
If you encounter any problems, please file an issue along with a detailed description.
This project was generated from Statistics Norway's SSB PyPI Template.