この記事について

テラデータの提供するPythonライブラリと、それらを使ってPythonからTeradataへ接続してデータを操作する方法を紹介します。

実行コードは、日本テラデータGitHub にて公開中しています。
このコードは無料のTeradata環境 ClearScape Analytics Experience にて実行可能です。

要点

teradatasql, teradatasqlalchemy, teradataml の3つのライブラリが提供されています
- teradatasql: 普通に connect して execute でクエリを実行する
- teradatasqlalchemy: SQLAlchemy の拡張。%sqlマジックが使えるように
- teradataml: 独自の pandas-like なAPI。直接クエリを書かない操作が可
スクリプト上でクエリを実行するだけの処理なら teradatasql がおすすめ
ノートブック上でクエリを実行してデータを抽出するなら %sql マジックが便利
複雑な分析には teradataml が便利だと思います

3つのライブラリ

ライブラリ名	特徴
teradatasql	Pythonの標準データベースAPI準拠 SQLによる分析、操作
teradatasqlalchemy	SqlAlchemyへのteradata拡張 SqlAlchemy独自のシンタックスによる分析、操作が可能 ipython-sql の %sql マジックが利用可能
teradataml	独自のpandas-like なデータ操作APIを提供クエリを直接書かないで分析が可能 sklearn-like なAPIでTeradataの機械学習機能が利用可能

インストール

ライブラリのインストール

pip install teradatasql teradatasqlalchemy teradataml "sqlalchemy<2"

sqlalchemy のバージョン2との互換性に不具合が出ているので、改善まではバージョン1を指定します

Teradataへの接続情報を指定 (共通)

実際の環境に合わせて接続情報を指定します。
わからなければデータベース管理者に聞いてみましょう。
この例はお試し環境 ClearScape Experience での設定です。

接続情報

from getpass import getpass

host = "host.docker.internal"
user = "demo_user"
database = "demo_user"
password = getpass("Password > ")
dbs_port = 1025
encryptdata = "true"

teradatasql

Pythonの標準データベース規格 (PEP 249) に準拠したコネクタを提供しています。

teradatasqlで接続

import teradatasql

with teradatasql.connect(host=host, user=user, password=password,
                         database=database, dbs_port=dbs_port,
                         encryptdata=encryptdata) as conn:
  c = conn.cursor()
  q = """SELECT * FROM dbc.dbcInfoV"""   # Teradataのバージョン情報を取得するクエリ
  c.execute(q)
  for row in c:
    print(row)

# ['VERSION', '17.20.03.23']
# ['LANGUAGE SUPPORT MODE', 'Standard']
# ['RELEASE', '17.20.03.23']

警告文はでますが、pandas.read_sql も使えます。

read_sql

with teradatasql.connect(host=host, user=user, password=password, database=database,
                         dbs_port=dbs_port, encryptdata=encryptdata) as conn:
  q = """SELECT * FROM dbc.dbcInfoV"""   # Teradataのバージョン情報を取得するクエリ
  x = pd.read_sql(q, conn)
x

# 	InfoKey                 InfoData
#0 	VERSION                 17.20.03.23
#1 	LANGUAGE SUPPORT MODE   Standard
#2 	RELEASE                 17.20.03.23

teradatasqlalchemy

SQLAlchemyでは、接続文字列を用いてデータベースに接続します。Teradataの場合は下記のような形式です。
パスワードに URLで使えない文字が含まれているとエラーになるので、urllib.parse.quote_plus でエンコードするのが安全です。

接続文字列の作成

from urllib.parse import quote_plus

connstr = (
  f"teradatasql://{user}:{quote_plus(password)}@{host}/?"
  f"&database={database}"
  f"&dbs_port={dbs_port}"
  f"&encryptdata={encryptdata}"
)

#teradatasql://demo_user:***@host.docker.internal/?database=demo_user&dbs_port=1025&encryptdata=true

この接続文字列を使って接続、クエリを実行することもできます。
その他にもSQLAlchemyならではのデータ操作の仕方があると思いますが、~~よく知らないので~~ 割愛します。

sqlalchemyで接続

from sqlalchemy import create_engine

engine = create_engine(connstr)
with engine.connect() as conn:
  q = """SELECT * FROM dbc.dbcInfoV"""   # Teradataのバージョン情報を取得するクエリ
  x = pd.read_sql(q, conn)
x

# 	InfoKey                 InfoData
#0 	VERSION                 17.20.03.23
#1 	LANGUAGE SUPPORT MODE   Standard
#2 	RELEASE                 17.20.03.23

SQLAlchemy 対応の恩恵の一つは、 ipython-sql の提供する %sql が使えることだと思います。
個人的に、データの抽出方法としては一番のおすすめです（Jupyterでしか使えませんが）。

%sql magic

%load_ext sql
%config SqlMagic.autopandas=True
# `autopandas=True` に設定すると、結果をDataFrameで返すようになり、後の操作に便利

%sql {connstr}
# 最初に接続文字列を渡す。次回以降クエリだけを渡せば良くなります

%sql SELECT * FROM dbc.dbcInfoV

# 	InfoKey                 InfoData
#0 	VERSION                 17.20.03.23
#1 	LANGUAGE SUPPORT MODE   Standard
#2 	RELEASE                 17.20.03.23

teradataml

teradataml はクエリを直接書かない、独自の pandas-like, sklearn-like なAPIを提供します。

teradataml では接続オブジェクトを作成するのではなく、コンテキスト（セッション）を張ってデータ操作をする形を取ります。

teradatamlセッション開始

from sqlalchemy import create_engine
from teradataml import create_context

# 最初に「コンテキスト」を作成して使います
# コンテキストの作成には、SqlAlchemyのエンジンを用いるのが楽です
engine = create_engine(connstr)
context = create_context(tdsqlengine=engine, temp_database_name=user)
# temp_database_name には、操作の過程で作成される中間テーブル・ビューの保存場所を指定します。
# ユーザーが CREATE VIEW, CREATE TABLE を行う権限を持っているデータベースを指定します

上記において、temp_database_name にはライブラリが自動で生成するテーブルやビューの保存場所を指定しています。通常は接続情報の user か database と同じにするのが良いと思います。

DataFrame の作成

teradataml ではテーブルを DataFrame オブジェクトに保持します。
下記はテーブル名を指定してデータを取得する方法です（"mtcars" はテーブル名）。

teradatamlでデータの取得

from teradataml import DataFrame

df = DataFrame("mtcars")
print(type(df))
df

#<class 'teradataml.dataframe.dataframe.DataFrame'>
#
#   id             mpg   cyl  disp   hp   drat  wt     qsec   vs  am  gear  carb
#0  Mazda RX4      21.0  6    160.0  110  3.90  2.620  16.46  0   1   4     4
#1  Mazda RX4 Wag  21.0  6    160.0  110  3.90  2.875  17.02  0   1   4     4
#2  Datsun 710     22.8  4    108.0  93   3.85  2.320  18.61  1   1   4     1
#....

テーブルのデータベース名を指定するときは注意が必要で、明示的にダブルクォートが必要です。

データベース名の指定

df = DataFrame('"dbc"."dbcInfoV"')

クエリで使うデータを指定することも可能です。

クエリによるデータ指定

df = DataFrame(query="""SELECT InfoData FROM dbc.dbcInfoV""")

DataFrameの裏側とデータ抽出

teradatamlのデータフレームは、データベース上のデータへのマッピングのみを保持していて、実際にデータを抽出しません（表示用のサンプル数行は除く）。
オブジェクト内にクエリを持っておき、必要に応じてそれを実行するという形式になっています。
そのクエリの内容は show_query メソッドから確認できます。

DataFrameのクエリ

x = DataFrame("mtcars")
x.show_query()
#'select * from "mtcars"'

データベースから実際にデータを抽出するには、to_pandasメソッドでメモリ上のpandas.DataFrameに持つか、to_csvメソッドでファイルに書き出します。

DataFrameからデータを抽出

data = x.to_pandas()      
# pandasのデータフレームを作る

x.to_csv("filename.csv", all_rows=True)  
# ファイルに書き出す
# 行数を指定することもできる
# 大量のデータの場合は、all_rows=True にすると時間がかかるので注意

大量データの抽出については、こちらのノートブックもご参照ください (2.1_load-and-read-with-python.ipynb, 2.2_load-and-read-with-tdload.ipynb)

pandas-likeなデータ操作

DataFrameでのデータ操作

x = DataFrame("mtcars")

# 列の選択
x[["id", "wt"]]
#id                 wt
#Datsun 710         2.32
#Hornet Sportabout  3.44
#Valiant            3.46
#...


# 行の抽出
x[x["cyl"] > 5]
#id              mpg   cyl  disp   hp   drat  wt     qsec   vs  am  gear  carb
#Hornet 4 Drive  21.4  6    258.0  110  3.08  3.215  19.44  1   0   3     1
#Valiant         18.1  6    225.0  105  2.76  3.46   20.22  1   0   3     1
#Duster 360      14.3  8    360.0  245  3.21  3.57   15.84  0   0   3     4
#...


# 変数を定義
x.assign(mpgx2=x["mpg"] * 2)
#id              mpg   cyl  disp   hp   drat  wt     qsec   vs  am  gear  carb  mpgx2
#Hornet 4 Drive  21.4  6    258.0  110  3.08  3.215  19.44  1   0   3     1     42.8 
#Valiant         18.1  6    225.0  105  2.76  3.46   20.22  1   0   3     1     36.2
#Duster 360      14.3  8    360.0  245  3.21  3.57   15.84  0   0   3     4     28.6
#...


# 集約
x.groupby("cyl").agg({
  "mpg": ["min", "mean", "max", "count"],
  "wt": ["mean", "median"]
})
#cyl  min_mpg  mean_mpg            max_mpg  count_mpg  mean_wt             median_wt
#6    17.8     19.74285714285714   21.4     7          3.117142857142857   3.215
#4    21.4     26.663636363636364  33.9     11         2.285727272727273   2.2
#8    10.4     15.100000000000003  19.2     14         3.9992142857142867  3.755

もちろん、結合 (join) もできます。

join

# 結合用のデータを作成
from teradataml import copy_to_sql

tmp = pd.DataFrame({"cyl": [4, 6, 8], "cyl_name": ["four", "six", "eight"], "cyl_name_ja": ["四", "六", "八"]})
copy_to_sql(tmp, "cylinfo", if_exists="replace", index=False, primary_index=None)

y = DataFrame("cylinfo")
z = x.join(y, on="cyl", how="inner", lsuffix="x", rsuffix="y")
z[["id", "cyl", "cyl_name", "cyl_name_ja"]]
#id                 x_cyl  cyl_name  cyl_name_ja
#Datsun 710         4      four      四
#Hornet Sportabout  8      eight     八
#Valiant            6      six       六
#...

ちょっと凝った例として、Window関数も使うことができます（個人的には Window関数はさすがにクエリのほうがわかりやすいとは思いますが…）。

window関数

# Window関数 (累積和)
(
  x[["cyl", "wt"]]
    .window(partition_columns="cyl", order_columns="wt", window_end_point=0)
    .sum()
    .sort(["cyl", "wt"])
)
#cyl  wt     cyl_sum  wt_sum
#4    1.513  4        1.513
#4    1.615  8        3.128
#4    1.835  12       4.9633
#4    1.935  16       6.898
#...

結び

以上、PythonからTeradata上のデータを操作する方法をご紹介しました。
手慣れたPython経由でクエリを実行したり、重めのデータ処理をデータベースで処理して軽くしたものを手元で扱ったりすると、データサイエンティストの日頃の仕事もちょっと楽になるのではないかと思います。
Pythonも楽しいですが、SQLもパワフルなので、ぜひお試しください。
疑問点・懸念点などありましたらぜひコメントください。GitHubの方にIssueを立てていただいても結構です。