datafusion-postgres

KO | EN

gitlite — search

#hacktoberfest #ai #llm #go #golang #android #deep-learning #linux #macos #java #windows #awesome #cli #pytorch

datafusion-postgres

(★ 158)

Postgres protocol frontend for DataFusion

File Explorer

Download Latest Version (.zip)

- config.toml
- - ci.yml
- dependabot.yml
- - datatypes.rs
  - encoder.rs
  - error.rs
  - geo_encoder.rs
  - lib.rs
  - list_encoder.rs
  - row_encoder.rs
  - struct_encoder.rs
- Cargo.toml
- - parser_benchmark.rs
- - pg_aggregate.feather
  - pg_am.feather
  - pg_amop.feather
  - pg_amproc.feather
  - pg_attrdef.feather
  - pg_attribute.feather
  - pg_auth_members.feather
  - pg_authid.feather
  - pg_cast.feather
  - pg_class.feather
  - pg_collation.feather
  - pg_constraint.feather
  - pg_conversion.feather
  - pg_database.feather
  - pg_db_role_setting.feather
  - pg_default_acl.feather
  - pg_depend.feather
  - pg_description.feather
  - pg_enum.feather
  - pg_event_trigger.feather
  - pg_extension.feather
  - pg_foreign_data_wrapper.feather
  - pg_foreign_server.feather
  - pg_foreign_table.feather
  - pg_get_keywords.feather
  - pg_index.feather
  - pg_inherits.feather
  - pg_init_privs.feather
  - pg_language.feather
  - pg_largeobject.feather
  - pg_largeobject_metadata.feather
  - pg_namespace.feather
  - pg_opclass.feather
  - pg_operator.feather
  - pg_opfamily.feather
  - pg_parameter_acl.feather
  - pg_partitioned_table.feather
  - pg_policy.feather
  - pg_proc.feather
  - pg_publication.feather
  - pg_publication_namespace.feather
  - pg_publication_rel.feather
  - pg_range.feather
  - pg_replication_origin.feather
  - pg_rewrite.feather
  - pg_seclabel.feather
  - pg_sequence.feather
  - pg_shdepend.feather
  - pg_shdescription.feather
  - pg_shseclabel.feather
  - pg_statistic.feather
  - pg_statistic_ext.feather
  - pg_statistic_ext_data.feather
  - pg_subscription.feather
  - pg_subscription_rel.feather
  - pg_tablespace.feather
  - pg_transform.feather
  - pg_trigger.feather
  - pg_ts_config.feather
  - pg_ts_config_map.feather
  - pg_ts_dict.feather
  - pg_ts_parser.feather
  - pg_ts_template.feather
  - pg_type.feather
  - pg_user_mapping.feather
- - lib.rs
  - pg_catalog.rs
  - sql.rs
- Cargo.toml
- export_pg_catalog_arrow.sh
- pg_to_arrow.py
- - enumerate_udfs.rs
- - array.rs
  - binary.rs
  - bitstring.rs
  - conditional.rs
  - crypto.rs
  - datetime.rs
  - enum_type.rs
  - format.rs
  - geometric.rs
  - json.rs
  - lib.rs
  - network.rs
  - numeric.rs
  - range.rs
  - row.rs
  - sequence.rs
  - string.rs
  - system.rs
  - text_search.rs
  - uuid.rs
  - xml.rs
- - sqllogictest.rs
- Cargo.toml
- functions.md
- - auth.rs
  - client.rs
  - handlers.rs
  - lib.rs
  - planner.rs
  - testing.rs
- - array_bounds.rs
  - dbeaver.rs
  - grafana.rs
  - metabase.rs
  - pgadbc.rs
  - pgadmin.rs
  - pgcli.rs
  - psql.rs
- Cargo.toml
- LICENSE-APACHE
- - main.rs
- Cargo.toml
- LICENSE-APACHE
- - server.crt
  - server.key
- all_types.parquet
- create_arrow_testfile.py
- delhiclimate.csv
- README.md
- test.sh
- test_csv.py
- test_fdw.py
- test_parquet.py
- test_postgis.py
- test_ssl.py
- test_transactions.py
.gitignore
Cargo.lock
Cargo.toml
flake.lock
flake.nix
LICENSE-APACHE
README.md

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# datafusion-postgres

[![Crates.io Version][crates-badge]][crates-url]
[![Docs.rs Version][docs-badge]][docs-url]

[crates-badge]: https://img.shields.io/crates/v/datafusion-postgres?label=datafusion-postgres
[crates-url]: https://crates.io/crates/datafusion-postgres
[docs-badge]: https://img.shields.io/docsrs/datafusion-postgres
[docs-url]: https://docs.rs/datafusion-postgres/latest/datafusion_postgres

A PostgreSQL-compatible server frontend for [Apache
DataFusion](https://datafusion.apache.org). Available as both a library and CLI
tool.

Built on [pgwire](https://github.com/sunng87/pgwire) to provide PostgreSQL wire
protocol compatibility for analytical workloads. It was originally an example of
the [pgwire](https://github.com/sunng87/pgwire) project.

## Scope of the Project

- `datafusion-postgres`: Postgres frontend for datafusion, as a library.
  - Serving Datafusion `SessionContext` with pgwire library
  - Customizible/Optional authentication and Permission control
- `datafusion-pg-catalog`: A Postgres compatible `pg_catalog` schema and
  functions for datafusion backend.
- `arrow-pg`: A data type mapping, encoding/decoding library for arrow and
  postgres(pgwire) data types.
- `datafusion-postgres-cli`: A cli tool starts a postgres compatible server for
  datafusion supported file formats, just like python's `SimpleHTTPServer`.

## Supported Database Clients

- Database Clients
  - [x] psql
  - [x] DBeaver
  - [x] pgcli
  - [x] VSCode SQLTools
  - [ ] Intellij Datagrip
- BI & Visualization
  - [x] Metabase
  - [ ] PowerBI
  - [x] Grafana

## Quick Start

### The Library `datafusion-postgres`

The high-level entrypoint of `datafusion-postgres` library is the `serve`
function which takes a datafusion `SessionContext` and some server configuration
options.

```rust
use std::sync::Arc;
use datafusion::prelude::SessionContext;
use datafusion_postgres::{serve, ServerOptions};
use datafusion_pg_catalog::setup_pg_catalog;

// Create datafusion SessionContext
let session_context = Arc::new(SessionContext::new());
// Configure your `session_context`
// ...

// Optional: setup pg_catalog schema
setup_pg_catalog(session_context, "datafusion")?;

// Start the Postgres compatible server with SSL/TLS
let server_options = ServerOptions::new()
    .with_host("127.0.0.1".to_string())
    .with_port(5432)
    // Optional: setup tls
    .with_tls_cert_path(Some("server.crt".to_string()))
    .with_tls_key_path(Some("server.key".to_string()));

serve(session_context, &server_options).await
```

### The CLI `datafusion-postgres-cli`

Command-line tool to serve JSON/CSV/Arrow/Parquet/Avro files as
PostgreSQL-compatible tables. This is like a `SimpleHTTPServer` for hosting data
files, but with Postgres protocol and datafusion query engine.

```
datafusion-postgres-cli 0.6.1
A PostgreSQL interface for DataFusion. Serve CSV/JSON/Arrow/Parquet files as tables.

USAGE:
    datafusion-postgres-cli [OPTIONS]

FLAGS:
    -h, --help       Prints help information
    -V, --version    Prints version information

OPTIONS:
        --arrow <arrow-tables>...        Arrow files to register as table, using syntax `table_name:file_path`
        --avro <avro-tables>...          Avro files to register as table, using syntax `table_name:file_path`
        --csv <csv-tables>...            CSV files to register as table, using syntax `table_name:file_path`
    -d, --dir <directory>                Directory to serve, all supported files will be registered as tables
        --host <host>                    Host address the server listens to [default: 127.0.0.1]
        --json <json-tables>...          JSON files to register as table, using syntax `table_name:file_path`
        --parquet <parquet-tables>...    Parquet files to register as table, using syntax `table_name:file_path`
    -p <port>                            Port the server listens to [default: 5432]
        --tls-cert <tls-cert>            Path to TLS certificate file for SSL/TLS encryption
        --tls-key <tls-key>              Path to TLS private key file for SSL/TLS encryption
```

#### Security Options

```bash
# Run with SSL/TLS encryption
datafusion-postgres-cli \
  --csv data:sample.csv \
  --tls-cert server.crt \
  --tls-key server.key

# Run without encryption (development only)
datafusion-postgres-cli --csv data:sample.csv
```

## Example Usage

### Basic Example

Host a CSV dataset as a PostgreSQL-compatible table:

```bash
datafusion-postgres-cli --csv climate:delhiclimate.csv
```

```
Loaded delhiclimate.csv as table climate
TLS not configured. Running without encryption.
Listening on 127.0.0.1:5432 (unencrypted)
```

### Connect with psql

```bash
psql -h 127.0.0.1 -p 5432 -U postgres
```

```sql
postgres=> SELECT COUNT(*) FROM climate;
 count
-------
  1462
(1 row)

postgres=> SELECT date, meantemp FROM climate WHERE meantemp > 35 LIMIT 5;
    date    | meantemp
------------+----------
 2017-05-15 |     36.9
 2017-05-16 |     37.9
 2017-05-17 |     38.6
 2017-05-18 |     37.4
 2017-05-19 |     35.4
(5 rows)

postgres=> BEGIN;
BEGIN
postgres=> SELECT AVG(meantemp) FROM climate;
       avg
------------------
 25.4955206557617
(1 row)
postgres=> COMMIT;
COMMIT
```

### SSL/TLS

```bash
# Generate SSL certificates
openssl req -x509 -newkey rsa:4096 -keyout server.key -out server.crt \
  -days 365 -nodes -subj "/C=US/ST=CA/L=SF/O=MyOrg/CN=localhost"

# Start secure server
datafusion-postgres-cli \
  --csv climate:delhiclimate.csv \
  --tls-cert server.crt \
  --tls-key server.key
```

```
Loaded delhiclimate.csv as table climate
TLS enabled using cert: server.crt and key: server.key
Listening on 127.0.0.1:5432 with TLS encryption
```

## PostGIS/Geodatafusion

With [geodatafusion](https://github.com/datafusion-contrib/geodatafusion), we
can also simulate PostGIS interface (UDF and datatypes) with
datafusion-postgres. To enable this feature, turn on the feature flag `postgis`
for `datafusion-postgres`.

## Community

### Developer Mailing List

If you like the idea of pgwire, datafusion-postgres and want to join the
development of the library, or its ecosystem integrations, extensions, you are
welcomed to join our developer mailing list: https://groups.io/g/pgwire-dev/

## License

This library is released under Apache license.

datafusion-postgres

File Explorer

Today's picks

Was this content helpful?

Recent Feedback

datafusion-postgres

File Explorer

Today's picks

Was this content helpful?

Recent Feedback

Download README