# Meltano/Singer Target for CrateDB
[](https://github.com/crate-workbench/meltano-target-cratedb/actions/workflows/main.yml)
[](https://codecov.io/gh/crate-workbench/meltano-target-cratedb/)
[](https://pypi.org/project/meltano-target-cratedb/)
[](https://github.com/crate-workbench/meltano-target-cratedb/blob/main/LICENSE)
[](https://pypi.org/project/meltano-target-cratedb/)
[](https://pypi.org/project/meltano-target-cratedb/)
[](https://pepy.tech/project/meltano-target-cratedb/)
## About
A [Singer] target for [CrateDB], built with the [Meltano SDK] for custom extractors
and loaders, and based on the [Meltano PostgreSQL target]. It connects a library of
[600+ connectors] with CrateDB, and vice versa.
In Singer ELT jargon, a "target" conceptually wraps a data sink, where you
"load" data into.
Singer, Meltano, and PipelineWise provide foundational components and
an integration engine for composable Open Source ETL with [600+ connectors].
On the database integration side, they are heavily based on [SQLAlchemy].
### CrateDB
[CrateDB] is a distributed and scalable SQL database for storing and analyzing
massive amounts of data in near real-time, even with complex queries. It is
PostgreSQL-compatible, and based on [Apache Lucene].
CrateDB offers a Python SQLAlchemy dialect, in order to plug into the
comprehensive Python data-science and -wrangling ecosystems.
### Singer
_The open-source standard for writing scripts that move data._
[Singer] is an open source specification and software framework for [ETL]/[ELT]
data exchange between a range of different systems. For talking to SQL databases,
it employs a metadata subsystem based on SQLAlchemy.
Singer reads and writes Singer-formatted messages, following the [Singer Spec].
Effectively, those are JSONL files.
### Meltano
_Unlock all the data that powers your data platform._
_Say goodbye to writing, maintaining, and scaling your own API integrations
with Meltano's declarative code-first data integration engine, bringing
600+ APIs and DBs to the table._
[Meltano] builds upon Singer technologies, uses configuration files in YAML
syntax instead of JSON, adds an improved SDK and other components, and runs
the central addon registry, [meltano | Hub].
### PipelineWise
[PipelineWise] is another Data Pipeline Framework using the Singer.io
specification to ingest and replicate data from various sources to
various destinations. The list of [PipelineWise Taps] include another
20+ high-quality data-source and -sink components.
### SQLAlchemy
[SQLAlchemy] is the leading Python SQL toolkit and Object Relational Mapper
that gives application developers the full power and flexibility of SQL.
It provides a full suite of well known enterprise-level persistence patterns,
designed for efficient and high-performing database access, adapted into a
simple and Pythonic domain language.
## Install
Usually, you will not install this package directly, but on behalf
of a Meltano definition instead, for example. A corresponding snippet
is outlined in the next section. After adding it to your `meltano.yml`
configuration file, you can install all defined components and their
dependencies.
```
meltano install
```
## Usage
You can run the CrateDB Singer target `target-cratedb` by itself, or
in a pipeline using Meltano.
### Meltano
Using the `meltano add` subcommand, you can add the plugin to your
Meltano project.
```shell
meltano add loader target-cratedb
```
NB: It will only work like this when released and registered on Meltano Hub.
In the meanwhile, please add the configuration snippet manually.
#### CrateDB Cloud
In order to connect to [CrateDB Cloud], configure the `sqlalchemy_url` setting
within your `meltano.yml` configuration file like this.
```yaml
- name: target-cratedb
namespace: cratedb
variant: cratedb
pip_url: meltano-target-cratedb
config:
sqlalchemy_url: "crate://admin:K4IgMXNvQBJM3CiElOiPHuSp6CiXPCiQYhB4I9dLccVHGvvvitPSYr1vTpt4@example.aks1.westeurope.azure.cratedb.net:4200?ssl=true"}
add_record_metadata: true
```
#### On localhost
In order to connect to a standalone or on-premise instance of CrateDB, configure
the `sqlalchemy_url` setting within your `meltano.yml` configuration file like this.
```yaml
- name: target-cratedb
namespace: cratedb
variant: cratedb
pip_url: meltano-target-cratedb
config:
sqlalchemy_url: crate://crate@localhost/
add_record_metadata: true
```
Then, invoke the pipeline by using `meltano run`, similar like this.
```shell
meltano run tap-xyz target-cratedb
```
### Standalone
You can also invoke it standalone by using the `target-cratedb` program.
This example demonstrates how to load a file into the database.
First, acquire an example file in Singer format, including the list of
countries of the world.
```shell
wget https://github.com/MeltanoLabs/target-postgres/raw/v0.0.9/target_postgres/tests/data_files/tap_countries.singer
```
Now, define the database connection string including credentials in
SQLAlchemy format.
```shell
echo '{"sqlalchemy_url": "crate://crate@localhost/"}' > settings.json
```
By using Unix pipes, load the data file into the database, referencing
the path to the configuration file.
```shell
cat tap_countries.singer | target-cratedb --config=settings.json
```
Using the interactive terminal program, `crash`, you can run SQL
statements on CrateDB.
```shell
pip install crash
crash --hosts localhost:4200
```
Now, you can verify that the data has been loaded correctly.
```sql
SELECT
"code", "name", "capital", "emoji", "languages[1]"
FROM
"melty"."countries"
ORDER BY
"name"
LIMIT
42;
```
## Development
In order to work on this adapter dialect on behalf of a real pipeline definition,
link your sandbox to a development installation of [meltano-target-cratedb], and
configure the `pip_url` of the component to point to a different location than the
[vanilla package on PyPI].
Use this URL to directly point to a specific Git repository reference.
```yaml
pip_url: git+https://github.com/crate-workbench/meltano-target-cratedb.git@main
```
Use a `pip`-like notation to link the CrateDB Singer target in development mode,
so you can work on it at the same time while running the pipeline, and iterating
on its definition.
```yaml
pip_url: --editable=/path/to/sources/meltano-target-cratedb
```
[600+ connectors]: https://hub.meltano.com/
[Apache Lucene]: https://lucene.apache.org/
[CrateDB]: https://cratedb.com/product
[CrateDB Cloud]: https://console.cratedb.cloud/
[ELT]: https://en.wikipedia.org/wiki/Extract,_load,_transform
[ETL]: https://en.wikipedia.org/wiki/Extract,_transform,_load
[Meltano]: https://meltano.com/
[meltano | Hub]: https://hub.meltano.com/
[Meltano SDK]: https://github.com/meltano/sdk
[Meltano PostgreSQL target]: https://pypi.org/project/meltanolabs-target-postgres/
[meltano-target-cratedb]: https://github.com/crate-workbench/meltano-target-cratedb
[Singer]: https://www.singer.io/
[Singer Spec]: https://hub.meltano.com/singer/spec/
[PipelineWise]: https://transferwise.github.io/pipelinewise/
[PipelineWise Taps]: https://transferwise.github.io/pipelinewise/user_guide/yaml_config.html
[SQLAlchemy]: https://www.sqlalchemy.org/
[vanilla package on PyPI]: https://pypi.org/project/meltano-target-cratedb/
Raw data
{
"_id": null,
"home_page": "",
"name": "meltano-target-cratedb",
"maintainer": "",
"docs_url": null,
"requires_python": "<3.12,>=3.8",
"maintainer_email": "",
"keywords": "cratedb,data-loading,data-processing,data-toolkit,data-transfer,data-transformation,ELT,ETL,io,Meltano,Meltano SDK,PostgreSQL,Singer",
"author": "",
"author_email": "Andreas Motl <andreas.motl@crate.io>",
"download_url": "https://files.pythonhosted.org/packages/29/a4/bc68a560ecd794b09177eb8b5e4ab3234d09cf5810f9c0394a7a5112d583/meltano-target-cratedb-0.0.1.tar.gz",
"platform": null,
"description": "# Meltano/Singer Target for CrateDB\n\n[](https://github.com/crate-workbench/meltano-target-cratedb/actions/workflows/main.yml)\n[](https://codecov.io/gh/crate-workbench/meltano-target-cratedb/)\n[](https://pypi.org/project/meltano-target-cratedb/)\n\n[](https://github.com/crate-workbench/meltano-target-cratedb/blob/main/LICENSE)\n[](https://pypi.org/project/meltano-target-cratedb/)\n[](https://pypi.org/project/meltano-target-cratedb/)\n[](https://pepy.tech/project/meltano-target-cratedb/)\n\n\n## About\n\nA [Singer] target for [CrateDB], built with the [Meltano SDK] for custom extractors\nand loaders, and based on the [Meltano PostgreSQL target]. It connects a library of\n[600+ connectors] with CrateDB, and vice versa.\n\nIn Singer ELT jargon, a \"target\" conceptually wraps a data sink, where you\n\"load\" data into.\n\nSinger, Meltano, and PipelineWise provide foundational components and\nan integration engine for composable Open Source ETL with [600+ connectors].\nOn the database integration side, they are heavily based on [SQLAlchemy].\n\n\n### CrateDB\n\n[CrateDB] is a distributed and scalable SQL database for storing and analyzing\nmassive amounts of data in near real-time, even with complex queries. It is\nPostgreSQL-compatible, and based on [Apache Lucene].\n\nCrateDB offers a Python SQLAlchemy dialect, in order to plug into the\ncomprehensive Python data-science and -wrangling ecosystems.\n\n### Singer\n\n_The open-source standard for writing scripts that move data._\n\n[Singer] is an open source specification and software framework for [ETL]/[ELT]\ndata exchange between a range of different systems. For talking to SQL databases,\nit employs a metadata subsystem based on SQLAlchemy.\n\nSinger reads and writes Singer-formatted messages, following the [Singer Spec].\nEffectively, those are JSONL files.\n\n### Meltano\n\n_Unlock all the data that powers your data platform._\n\n_Say goodbye to writing, maintaining, and scaling your own API integrations\nwith Meltano's declarative code-first data integration engine, bringing\n600+ APIs and DBs to the table._\n\n[Meltano] builds upon Singer technologies, uses configuration files in YAML\nsyntax instead of JSON, adds an improved SDK and other components, and runs\nthe central addon registry, [meltano | Hub].\n\n### PipelineWise\n\n[PipelineWise] is another Data Pipeline Framework using the Singer.io\nspecification to ingest and replicate data from various sources to\nvarious destinations. The list of [PipelineWise Taps] include another\n20+ high-quality data-source and -sink components.\n\n### SQLAlchemy\n\n[SQLAlchemy] is the leading Python SQL toolkit and Object Relational Mapper\nthat gives application developers the full power and flexibility of SQL.\n\nIt provides a full suite of well known enterprise-level persistence patterns,\ndesigned for efficient and high-performing database access, adapted into a\nsimple and Pythonic domain language.\n\n\n## Install\n\nUsually, you will not install this package directly, but on behalf\nof a Meltano definition instead, for example. A corresponding snippet\nis outlined in the next section. After adding it to your `meltano.yml`\nconfiguration file, you can install all defined components and their\ndependencies.\n```\nmeltano install\n```\n\n\n## Usage\n\nYou can run the CrateDB Singer target `target-cratedb` by itself, or\nin a pipeline using Meltano.\n\n### Meltano\n\nUsing the `meltano add` subcommand, you can add the plugin to your\nMeltano project.\n```shell\nmeltano add loader target-cratedb\n```\nNB: It will only work like this when released and registered on Meltano Hub.\n In the meanwhile, please add the configuration snippet manually.\n\n\n#### CrateDB Cloud\n\nIn order to connect to [CrateDB Cloud], configure the `sqlalchemy_url` setting\nwithin your `meltano.yml` configuration file like this.\n```yaml\n- name: target-cratedb\n namespace: cratedb\n variant: cratedb\n pip_url: meltano-target-cratedb\n config:\n sqlalchemy_url: \"crate://admin:K4IgMXNvQBJM3CiElOiPHuSp6CiXPCiQYhB4I9dLccVHGvvvitPSYr1vTpt4@example.aks1.westeurope.azure.cratedb.net:4200?ssl=true\"}\n add_record_metadata: true\n```\n\n\n#### On localhost\nIn order to connect to a standalone or on-premise instance of CrateDB, configure\nthe `sqlalchemy_url` setting within your `meltano.yml` configuration file like this.\n```yaml\n- name: target-cratedb\n namespace: cratedb\n variant: cratedb\n pip_url: meltano-target-cratedb\n config:\n sqlalchemy_url: crate://crate@localhost/\n add_record_metadata: true\n```\n\nThen, invoke the pipeline by using `meltano run`, similar like this.\n```shell\nmeltano run tap-xyz target-cratedb\n```\n\n### Standalone\n\nYou can also invoke it standalone by using the `target-cratedb` program.\nThis example demonstrates how to load a file into the database.\n\nFirst, acquire an example file in Singer format, including the list of\ncountries of the world.\n```shell\nwget https://github.com/MeltanoLabs/target-postgres/raw/v0.0.9/target_postgres/tests/data_files/tap_countries.singer\n```\n\nNow, define the database connection string including credentials in\nSQLAlchemy format.\n```shell\necho '{\"sqlalchemy_url\": \"crate://crate@localhost/\"}' > settings.json\n```\n\nBy using Unix pipes, load the data file into the database, referencing\nthe path to the configuration file.\n```shell\ncat tap_countries.singer | target-cratedb --config=settings.json\n```\n\nUsing the interactive terminal program, `crash`, you can run SQL\nstatements on CrateDB.\n```shell\npip install crash\ncrash --hosts localhost:4200\n```\n\nNow, you can verify that the data has been loaded correctly.\n```sql\nSELECT\n \"code\", \"name\", \"capital\", \"emoji\", \"languages[1]\"\nFROM\n \"melty\".\"countries\"\nORDER BY\n \"name\"\nLIMIT\n 42;\n```\n\n\n## Development\n\nIn order to work on this adapter dialect on behalf of a real pipeline definition,\nlink your sandbox to a development installation of [meltano-target-cratedb], and\nconfigure the `pip_url` of the component to point to a different location than the\n[vanilla package on PyPI].\n\nUse this URL to directly point to a specific Git repository reference.\n```yaml\npip_url: git+https://github.com/crate-workbench/meltano-target-cratedb.git@main\n```\n\nUse a `pip`-like notation to link the CrateDB Singer target in development mode,\nso you can work on it at the same time while running the pipeline, and iterating\non its definition.\n```yaml\npip_url: --editable=/path/to/sources/meltano-target-cratedb\n```\n\n\n[600+ connectors]: https://hub.meltano.com/\n[Apache Lucene]: https://lucene.apache.org/\n[CrateDB]: https://cratedb.com/product\n[CrateDB Cloud]: https://console.cratedb.cloud/\n[ELT]: https://en.wikipedia.org/wiki/Extract,_load,_transform\n[ETL]: https://en.wikipedia.org/wiki/Extract,_transform,_load\n[Meltano]: https://meltano.com/\n[meltano | Hub]: https://hub.meltano.com/\n[Meltano SDK]: https://github.com/meltano/sdk\n[Meltano PostgreSQL target]: https://pypi.org/project/meltanolabs-target-postgres/\n[meltano-target-cratedb]: https://github.com/crate-workbench/meltano-target-cratedb\n[Singer]: https://www.singer.io/\n[Singer Spec]: https://hub.meltano.com/singer/spec/\n[PipelineWise]: https://transferwise.github.io/pipelinewise/\n[PipelineWise Taps]: https://transferwise.github.io/pipelinewise/user_guide/yaml_config.html\n[SQLAlchemy]: https://www.sqlalchemy.org/\n[vanilla package on PyPI]: https://pypi.org/project/meltano-target-cratedb/\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "A Singer target for CrateDB, built with the Meltano SDK, and based on the Meltano PostgreSQL target.",
"version": "0.0.1",
"project_urls": {
"changelog": "https://github.com/crate-workbench/meltano-target-cratedb/blob/main/CHANGES.md",
"documentation": "https://github.com/crate-workbench/meltano-target-cratedb",
"homepage": "https://github.com/crate-workbench/meltano-target-cratedb",
"repository": "https://github.com/crate-workbench/meltano-target-cratedb"
},
"split_keywords": [
"cratedb",
"data-loading",
"data-processing",
"data-toolkit",
"data-transfer",
"data-transformation",
"elt",
"etl",
"io",
"meltano",
"meltano sdk",
"postgresql",
"singer"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "288d58e9fdc80484f832cb429b6a59f4a0e0822438f1e7dded596878f686b459",
"md5": "88bd793c2cf453527d1c4315e6af0441",
"sha256": "ee338d60f8ae30111ba968ab7eb84829bbfd6408646471d1e1ae7c97e7007a20"
},
"downloads": -1,
"filename": "meltano_target_cratedb-0.0.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "88bd793c2cf453527d1c4315e6af0441",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": "<3.12,>=3.8",
"size": 20516,
"upload_time": "2023-12-08T20:50:16",
"upload_time_iso_8601": "2023-12-08T20:50:16.852740Z",
"url": "https://files.pythonhosted.org/packages/28/8d/58e9fdc80484f832cb429b6a59f4a0e0822438f1e7dded596878f686b459/meltano_target_cratedb-0.0.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "29a4bc68a560ecd794b09177eb8b5e4ab3234d09cf5810f9c0394a7a5112d583",
"md5": "5701d71272fdafecdbfc9d7de647994f",
"sha256": "ae7d3a6ece37a38dd39e3ecb45d7d7104d17648ec297195ea9196dc745ab0f1f"
},
"downloads": -1,
"filename": "meltano-target-cratedb-0.0.1.tar.gz",
"has_sig": false,
"md5_digest": "5701d71272fdafecdbfc9d7de647994f",
"packagetype": "sdist",
"python_version": "source",
"requires_python": "<3.12,>=3.8",
"size": 20210,
"upload_time": "2023-12-08T20:50:19",
"upload_time_iso_8601": "2023-12-08T20:50:19.043462Z",
"url": "https://files.pythonhosted.org/packages/29/a4/bc68a560ecd794b09177eb8b5e4ab3234d09cf5810f9c0394a7a5112d583/meltano-target-cratedb-0.0.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-12-08 20:50:19",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "crate-workbench",
"github_project": "meltano-target-cratedb",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "meltano-target-cratedb"
}
JSON
