#odbc #parquet #sql

app odbc2parquet

Query an ODBC data source and store the result in a Parquet file

31 releases (5 breaking)

0.6.7 Jul 20, 2021
0.6.5 Jun 3, 2021
0.5.10 Mar 25, 2021
0.5.1 Dec 10, 2020
0.4.0 Nov 21, 2020

#52 in Command line utilities

Download history 68/week @ 2021-04-07 104/week @ 2021-04-14 61/week @ 2021-04-21 49/week @ 2021-04-28 35/week @ 2021-05-05 75/week @ 2021-05-12 19/week @ 2021-05-19 18/week @ 2021-05-26 31/week @ 2021-06-02 45/week @ 2021-06-09 30/week @ 2021-06-16 20/week @ 2021-06-23 37/week @ 2021-06-30 23/week @ 2021-07-07 58/week @ 2021-07-14 34/week @ 2021-07-21

149 downloads per month

MIT license

93KB
2K SLoC

ODBC to Parquet

Minimum required rust version

A command line tool to query an ODBC data source and write the result into a parquet file.

  • Small memory footprint. Only holds one batch at a time in memory.
  • Fast. Makes efficient use of ODBC bulk reads, to lower IO overhead.
  • Flexible. Query any ODBC data source you have a driver for. MySQL, MS SQL, Excel, ...

Mapping of types in queries

The tool queries the ODBC Data source for type information and maps it to parquet type as such:

ODBC SQL Type Parquet Type
Decimal(p, s) Decimal(p,s)
Numeric(p, s) Decimal(p,s)
Bit Boolean
Double Double
Real Float
Float Float
Tiny Integer Int8
Small Integer Int16
Integer Int32
Big Int Int64
Date Date
Timestamp(p: 0..3) Timestamp Milliseconds
Timestamp(p >= 4) Timestamp Microseconds
Varbinary Byte Array
Binary Fixed Length Byte Array
All others Utf8 Byte Array

p is short for precision. s is short for scale. Intervals are inclusive.

Installation

Prerequisites

To work with this tool you need an ODBC driver manager and an ODBC driver for the data source you want to access.

Windows

An ODBC driver manager is already preinstalled on windows. So is the ODBC data sources (64Bit) and ODBC data sources (32Bit) app which you can use to discover which drivers are already available on your system.

Linux

This tool links both at runtime and during build against libodbc.so. To get it you should install unixODBC. You can do this using your systems packet manager. For ubuntu you run:

sudo apt install unixodbc-dev

OS-X

This tool links both at runtime and during build against libodbc.so. To get it you should install unixODBC. To install it I recommend the homebrew packet manager, which allows you to install it using:

brew install unixodbc

Download binary from GitHub

https://github.com/pacman82/odbc2parquet/releases/latest

Note: Download the 32 Bit version if you want to connect to data sources using 32 Bit drivers and download the 64 Bit version if you want to connect via 64 Bit drivers. It won't work vice versa.

Via Cargo

If you have a rust tool chain installed, you can install this tool via cargo.

cargo install odbc2parquet

You can install cargo from here https://rustup.rs/.

Usage

Query using connection string

odbc2parquet query \
--connection-string "Driver={ODBC Driver 17 for SQL Server};Server=localhost;UID=SA;PWD=<YourStrong@Passw0rd>;" \
out.par  \
"SELECT * FROM Birthdays"

Query using data source name

odbc2parquet query \
--dsn my_db \
--password "<YourStrong@Passw0rd>" \
--user "SA" \
out.par1 \
"SELECT * FROM Birthdays"

List available ODBC drivers

odbc2parquet list-drivers

List available ODBC data sources

odbc2parquet list-data-sources

Use parameters in query

odbc2parquet query \
--connection-string "Driver={ODBC Driver 17 for SQL Server};Server=localhost;UID=SA;PWD=<YourStrong@Passw0rd>;" \
out.par  \
"SELECT * FROM Birthdays WHERE year > ? and year < ?" \
1990 2010

Inserting data into a database

odbc2parquet insert \
--connection-string "Driver={ODBC Driver 17 for SQL Server};Server=localhost;UID=SA;PWD=<YourStrong@Passw0rd>;" \
input.par \
MyTable

Use odbc2parquet --help to see all option.

Links

Thanks to @samaguire there is a script for Powershell users which helps you to download a bunch of tables to a folder: https://github.com/samaguire/odbc2parquet-PSscripts

Dependencies

~27MB
~555K SLoC