I'm fritz. I am a technical data engineer / passionate technologist who loves tackling interesting problems with code 😀. Some of the projects I am most proud of are:
Sling (https://github.com/slingdata-io) -- Extract/Load into your database with ease from your shell terminal.
- Database to Database
- File System to Database
- Database to File System
Ever wanted to quickly pipe in a CSV or JSON file into your database? Use sling to do so:
cat my_file.csv | sling run --tgt-conn MYDB --tgt-object my_schema.my_tableOr want to copy data between two databases? Do it with sling:
sling run --src-conn PG_DB --src-stream public.transactions --tgt-conn MYSQL_DB --tgt-object mysql.bank_transactions --mode full-refreshSling can also easily manage our local connections with the sling conns command:
$ sling conns set MY_PG url='postgresql://postgres:myPassword@pghost:5432/postgres'
$ sling conns list
+--------------------------+-----------------+-------------------+
| CONN NAME | CONN TYPE | SOURCE |
+--------------------------+-----------------+-------------------+
| AWS_S3 | FileSys - S3 | sling env yaml |
| FINANCE_BQ | DB - BigQuery | sling env yaml |
| DO_SPACES | FileSys - S3 | sling env yaml |
| LOCALHOST_DEV | DB - PostgreSQL | dbt profiles yaml |
| MSSQL | DB - SQLServer | sling env yaml |
| MYSQL | DB - MySQL | sling env yaml |
| ORACLE_DB | DB - Oracle | env variable |
| MY_PG | DB - PostgreSQL | sling env yaml |
+--------------------------+-----------------+-------------------+
$ sling conns discover LOCALHOST_DEV
9:05AM INF Found 344 streams:
- "public"."accounts"
- "public"."bills"
- "public"."connections"
...dbREST (https://github.com/flarco/dbREST) -- Instantly spin up a REST API for any major database.
dbREST is basically an API backend that you can put in front of your database. Ever wanted to spin up an API service in front of your Snowflake, MySQL or even SQLite database? Well, dbREST allows that! Running dbrest serve will launch an API process which allow you to:
Select a table's data
GET /snowflake_db/my_schema/docker_logs?.columns=container_name,timestamp&.limit=100[
{ "container_name": "vector", "timestamp": "2022-04-22T23:54:06.644268688Z" },
{ "container_name": "postgres", "timestamp": "2022-04-22T23:54:06.644315426Z" },
{ "container_name": "api", "timestamp": "2022-04-22T23:54:06.654821046Z" },
]Insert into a table
POST /snowflake_db/my_schema/docker_logs
[
{"container_name":"vector","host":"vector","image":"timberio/vector:0.21.1-debian","message":"2022-04-22T23:54:06.644214Z INFO vector::sources::docker_logs: Capturing logs from now on. now=2022-04-22T23:54:06.644150817+00:00","stream":"stderr","timestamp":"2022-04-22T23:54:06.644268688Z"}
]Update a table
PATCH /snowflake_db/my_schema/my_table?.key=col1
[
{ "col1": "123", "timestamp": "2022-04-22T23:54:06.644268688Z" },
{ "col1": "124", "timestamp": "2022-04-22T23:54:06.644315426Z" },
{ "col1": "125", "timestamp": "2022-04-22T23:54:06.654821046Z" }
]Upsert into a table
PUT /snowflake_db/my_schema/my_table?.key=col1
[
{ "col1": "123", "timestamp": "2022-04-22T23:54:06.644268688Z" },
{ "col1": "124", "timestamp": "2022-04-22T23:54:06.644315426Z" },
{ "col1": "125", "timestamp": "2022-04-22T23:54:06.654821046Z" }
]Submit a Custom SQL query
POST /snowflake_db/.sql
select * from my_schema.docker_logs where timestamp is not null[
{ "container_name": "vector", "timestamp": "2022-04-22T23:54:06.644268688Z" },
{ "container_name": "postgres", "timestamp": "2022-04-22T23:54:06.644315426Z" },
{ "container_name": "api", "timestamp": "2022-04-22T23:54:06.654821046Z" },
]List all columns in a table
GET /snowflake_db/my_schema/docker_logs/.columns[
{"column_id":1,"column_name":"timestamp", "column_type":"String", "database_name":"default", "schema_name":"my_schema", "table_name":"docker_logs", "table_type":"table"},
{"column_id":2,"column_name":"container_name", "column_type":"String", "database_name":"default", "schema_name":"my_schema", "table_name":"docker_logs", "table_type":"table"},
{"column_id":3,"column_name":"host", "column_type":"String", "database_name":"default", "schema_name":"my_schema", "table_name":"docker_logs", "table_type":"table"},{"column_id":4,"column_name":"image", "column_type":"String", "database_name":"default", "schema_name":"my_schema", "table_name":"docker_logs", "table_type":"table"},
]List all tables in a schema
GET /snowflake_db/my_schema/.tables[
{"database_name":"default", "is_view":"table", "schema_name":"my_schema", "table_name":"docker_logs"},
{"database_name":"default", "is_view":"table", "schema_name":"my_schema", "table_name":"example"},
{"database_name":"default", "is_view":"view", "schema_name":"my_schema", "table_name":"place_vw"}
]List all columns, in all tables in a schema
GET /snowflake_db/my_schema/.columns[
{"column_id":1,"column_name":"timestamp", "column_type":"String", "database_name":"default", "schema_name":"my_schema", "table_name":"docker_logs", "table_type":"table"},
{"column_id":2,"column_name":"container_name", "column_type":"String", "database_name":"default", "schema_name":"my_schema", "table_name":"docker_logs", "table_type":"table"},
{"column_id":3,"column_name":"host", "column_type":"String", "database_name":"default", "schema_name":"my_schema", "table_name":"docker_logs", "table_type":"table"},{"column_id":4,"column_name":"image", "column_type":"String", "database_name":"default", "schema_name":"my_schema", "table_name":"docker_logs", "table_type":"table"},
]Of course there must be an authentication / authorization logic. It is based on tokens being issued with the dbrest token sub-command which are tied to roles defined in a YAML config file:
reader:
snowflake_db:
allow_read:
- schema1.*
- schema2.table1
allow_sql: 'disable'
my_pg:
allow_read:
- '*'
allow_sql: 'disable'
writer:
snowflake_db:
allow_read:
- schema1.*
- schema2.table1
allow_write:
- schema2.table3
allow_sql: 'disable'
my_pg:
allow_read:
- '*'
allow_write:
- '*'
allow_sql: 'any' We can now issue tokens with dbrest token issue <token_name> --roles reader,writer.
It is built in Go. And as you might have guessed, it also powers alot of dbNet :).
dbREST is in active developement. Here are some of the databases it connects to:
- Clickhouse
- Google BigQuery
- Google BigTable
- MySQL
- Oracle
- Redshift
- PostgreSQL
- SQLite
- SQL Server
- Snowflake
- DuckDB (coming soon)
- ScyllaDB (coming soon)
- Firebolt (coming soon)
- Databricks (coming soon)
dbNET (https://github.com/dbnet-io/dbnet) -- A database web client that lets your SQL superpowers shine.
The goal is to make it a great SQL IDE which gives useful context as you hover table and column names for example. It should allow you to ingest files with ease, imagine drag-dropping a CSV file into a schema where dbNet auto-creates the table with proper column types. The other nifty part is that it can run from a shell/terminal on any machine and lets users access the UI from the browser (with dbnet serve).
dbNet is in active developement and will be open-sourced soon. Here are some of the databases it connects to:
- Clickhouse
- Google BigQuery
- Google BigTable
- MySQL
- Oracle
- Redshift
- PostgreSQL
- SQLite
- SQL Server
- Snowflake
- DuckDB (coming soon)
- ScyllaDB (coming soon)
- Firebolt (coming soon)
- Databricks (coming soon)
