Traffine I/O

日本語

2022-10-01

Redshift Data API

AWS
AWS
Amazon Redshift
Amazon Redshift

Amazon Redshift Data APIとは

Amazon Redshift Data APIは、Amazon Web Services（AWS）が提供するサービスであり、開発者が自身のAmazon Redshiftデータウェアハウス上でSQLクエリをより簡単かつ安全に実行できるようにします。このサービスは、Python、Go、Java、Node.js、PHP、Ruby、C++などのAWS SDKでサポートされているプログラミング言語向けに、データのアクセス、インジェスト、イグレスのプロセスを簡素化します。

RedshiftデータAPIの主な利点の1つは、複雑なデータベース接続管理の必要性を排除することです。JDBCやODBC接続を確立する代わりに、開発者は単純にAPIを使用してSQLコマンドを実行することができます。

データAPIは非同期です。つまり、オープンな接続を保持せずに長時間のクエリを実行することができます。クエリの結果は24時間保存され、これらの結果を直接S3バケットにエクスポートすることもできます。また、AWS Secrets ManagerおよびAWS Identity and Access Management（IAM）との統合もスムーズであり、データベースの資格情報の管理がより簡単かつ安全に行えます。

使用可能なコマンド

以下は、Amazon Redshift Data APIで使用可能ないくつかのコマンドの例です。

SQLステートメントを実行

bash
$ aws redshift-data execute-statement
    --region us-west-2
    --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn
    --cluster-identifier mycluster-test
    --sql "SELECT :colname, COUNT(*) FROM demo_table WHERE trip_distance > :distance"
    --parameters "[{\"name\": \"colname\", \"value\": \"ratecode\"}, \ {\"name\": \"distance\", \"value\": \"5\"}]"
    --database dev

{
  "ClusterIdentifier": "mycluster-test",
  "CreatedAt": 1598323175.823,
  "Database": "dev",
  "Id": "c016234e-5c6c-4bc5-bb16-2c5b8ff61814",
  "SecretArn": "arn:aws:secretsmanager:us-west-2:123456789012:secret:yanruiz-secret-hKgPWn"
}

複数のSQLステートメントを実行

bash
$ aws redshift-data batch-execute-statement
    --region us-west-2
    --db-user myuser
    --cluster-identifier mycluster-test
    --database dev
    --sqls "set timezone to BST" "select * from mytable" "select * from another_table"

{
  "ClusterIdentifier": "mycluster-test",
  "CreatedAt": 1598306924.632,
  "Database": "dev",
  "DbUser": "myuser",
  "Id": "d9b6c0c9-0747-4bf4-b142-e8883122f766"
}

実行中のクエリをキャンセル

bash
$ aws redshift-data cancel-statement
    --id d9b6c0c9-0747-4bf4-b142-e8883122f766

SQLステートメントの結果を取得

bash
$ aws redshift-data get-statement-result
    --id d9b6c0c9-0747-4bf4-b142-e8883122f766
    --region us-west-2

{
  "ColumnMetadata": [
    {
      "isCaseSensitive": false,
      "isCurrency": false,
      "isSigned": true,
      "label": "userid",
      "length": 0,
      "name": "userid",
      "nullable": 0,
      "precision": 10,
      "scale": 0,
      "schemaName": "",
      "tableName": "stll_query",
      "typeName": "int4"
    },
    {
      "isCaseSensitive": true,
      "isCurrency": false,
      "isSigned": false,
      "label": "label",
      "length": 0,
      "name": "label",
      "nullable": 0,
      "precision": 320,
      "scale": 0,
      "schemaName": "",
      "tableName": "stll_query",
      "typeName": "bpchar"
    }
  ],
  "Records": [
    [
      {
        "longValue": 1
      },
      {
        "stringValue": "health"
      }
    ]
  ],
  "TotalNumRows": 1
}

SQLステートメントに関するメタデータを記述

bash
$ aws redshift-data describe-statement
    --id d9b6c0c9-0747-4bf4-b142-e8883122f766
    --region us-west-2

{
  "ClusterIdentifier": "mycluster-test",
  "CreatedAt": 1598306924.632,
  "Duration": 1095981511,
  "Id": "d9b6c0c9-0747-4bf4-b142-e8883122f766",
  "QueryString": "select * from stl_query limit 1",
  "RedshiftPid": 20859,
  "RedshiftQueryId": 48879,
  "ResultRows": 1,
  "ResultSize": 4489,
  "Status": "FINISHED",
  "UpdatedAt": 1598306926.667
}

テーブルの説明

bash
$ aws redshift-data describe-table
    --region us-west-2
    --cluster-identifier mycluster-test
    --database dev
    --schema information_schema
    --table sql_features
    --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn

{
  "ColumnList": [
    {
      "isCaseSensitive": false,
      "isCurrency": false,
      "isSigned": false,
      "length": 2147483647,
      "name": "feature_id",
      "nullable": 1,
      "precision": 2147483647,
      "scale": 0,
      "schemaName": "information_schema",
      "tableName": "sql_features",
      "typeName": "character_data"
    },
    {
      "isCaseSensitive": false,
      "isCurrency": false,
      "isSigned": false,
      "length": 2147483647,
      "name": "feature_name",
      "nullable": 1,
      "precision": 2147483647,
      "scale": 0,
      "schemaName": "information_schema",
      "tableName": "sql_features",
      "typeName": "character_data"
    }
  ]
}

SQL文に関するメタデータを一覧表示

bash
$ aws redshift-data list-statements
    --region us-west-2
    --status ALL

{
  "Statements": [
    {
      "CreatedAt": 1598306924.632,
      "Id": "d9b6c0c9-0747-4bf4-b142-e8883122f766",
      "QueryString": "select * from stl_query limit 1",
      "Status": "FINISHED",
      "UpdatedAt": 1598306926.667
    },
    {
      "CreatedAt": 1598311717.437,
      "Id": "e0ebd578-58b3-46cc-8e52-8163fd7e01aa",
      "QueryString": "select * from stl_query limit 1",
      "Status": "FAILED",
      "UpdatedAt": 1598311719.008
    },
    {
      "CreatedAt": 1598313683.65,
      "Id": "c361d4f7-8c53-4343-8c45-6b2b1166330c",
      "QueryString": "select * from stl_query limit 1",
      "Status": "ABORTED",
      "UpdatedAt": 1598313685.495
    },
    {
      "CreatedAt": 1598306653.333,
      "Id": "a512b7bd-98c7-45d5-985b-a715f3cfde7f",
      "QueryString": "select 1",
      "Status": "FINISHED",
      "UpdatedAt": 1598306653.992
    }
  ]
}

クラスター内のデータベースを一覧表示

bash
$ aws redshift-data list-databases
    --region us-west-2
    --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn
    --cluster-identifier mycluster-test
    --database dev

{
  "Databases": ["dev"]
}

データベース内のスキーマを一覧表示

bash
$ aws redshift-data list-schemas
    --region us-west-2
    --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn
    --cluster-identifier mycluster-test
    --database dev

{
  "Schemas": ["information_schema", "pg_catalog", "pg_internal", "public"]
}

データベース内のテーブルを一覧表示

bash
$ aws redshift-data list-tables
    --region us-west-2
    --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn
    --cluster-identifier mycluster-test
    --database dev
    --schema information_schema

{
  "Tables": [
    {
      "name": "sql_features",
      "schema": "information_schema",
      "type": "SYSTEM TABLE"
    },
    {
      "name": "sql_implementation_info",
      "schema": "information_schema",
      "type": "SYSTEM TABLE"
    }
  ]
}

参考

https://docs.aws.amazon.com/redshift/latest/mgmt/data-api.html
https://awscli.amazonaws.com/v2/documentation/api/latest/reference/redshift-data/index.html
https://docs.aws.amazon.com/redshift/latest/mgmt/data-api-calling.html

AWS Cognitoの使い方

AWS Control Towerとは

AlloyDB
AlloyDB
Amazon Cognito
Amazon Cognito
Amazon EC2
Amazon EC2
Amazon ECS
Amazon ECS
Amazon QuickSight
Amazon QuickSight
Amazon RDS
Amazon RDS
Amazon Redshift
Amazon Redshift
Amazon S3
Amazon S3
API
API
Autonomous Vehicle
Autonomous Vehicle
AWS
AWS
AWS API Gateway
AWS API Gateway
AWS Chalice
AWS Chalice
AWS Control Tower
AWS Control Tower
AWS IAM
AWS IAM
AWS Lambda
AWS Lambda
AWS VPC
AWS VPC
BERT
BERT
BigQuery
BigQuery
Causal Inference
Causal Inference
ChatGPT
ChatGPT
Chrome Extension
Chrome Extension
CircleCI
CircleCI
Classification
Classification
Cloud Functions
Cloud Functions
Cloud IAM
Cloud IAM
Cloud Run
Cloud Run
Cloud Storage
Cloud Storage
Clustering
Clustering
CSS
CSS
Data Engineering
Data Engineering
Data Modeling
Data Modeling
Database
Database
dbt
dbt
Decision Tree
Decision Tree
Deep Learning
Deep Learning
Descriptive Statistics
Descriptive Statistics
Differential Equation
Differential Equation
Dimensionality Reduction
Dimensionality Reduction
Discrete Choice Model
Discrete Choice Model
Docker
Docker
Economics
Economics
FastAPI
FastAPI
Firebase
Firebase
GIS
GIS
git
git
GitHub
GitHub
GitHub Actions
GitHub Actions
Google
Google
Google Cloud
Google Cloud
Google Search Console
Google Search Console
Hugging Face
Hugging Face
Hypothesis Testing
Hypothesis Testing
Inferential Statistics
Inferential Statistics
Interval Estimation
Interval Estimation
JavaScript
JavaScript
Jinja
Jinja
Kedro
Kedro
Kubernetes
Kubernetes
LightGBM
LightGBM
Linux
Linux
LLM
LLM
Mac
Mac
Machine Learning
Machine Learning
Macroeconomics
Macroeconomics
Marketing
Marketing
Mathematical Model
Mathematical Model
Meltano
Meltano
MLflow
MLflow
MLOps
MLOps
MySQL
MySQL
NextJS
NextJS
NLP
NLP
Nodejs
Nodejs
NoSQL
NoSQL
ONNX
ONNX
OpenAI
OpenAI
Optimization Problem
Optimization Problem
Optuna
Optuna
Pandas
Pandas
Pinecone
Pinecone
PostGIS
PostGIS
PostgreSQL
PostgreSQL
Probability Distribution
Probability Distribution
Product
Product
Project
Project
Psychology
Psychology
Python
Python
PyTorch
PyTorch
QGIS
QGIS
R
R
ReactJS
ReactJS
Regression
Regression
Rideshare
Rideshare
SEO
SEO
Singer
Singer
sklearn
sklearn
Slack
Slack
Snowflake
Snowflake
Software Development
Software Development
SQL
SQL
Statistical Model
Statistical Model
Statistics
Statistics
Streamlit
Streamlit
Tabular
Tabular
Tailwind CSS
Tailwind CSS
TensorFlow
TensorFlow
Terraform
Terraform
Transportation
Transportation
TypeScript
TypeScript
Urban Planning
Urban Planning
Vector Database
Vector Database
Vertex AI
Vertex AI
VSCode
VSCode
XGBoost
XGBoost

Ryusei Kakujo

researchgatelinkedingithub

Focusing on data science for mobility

Bench Press 100kg!