Traffine I/O

Bahasa Indonesia

2022-10-01

Redshift Data API

AWS
AWS
Amazon Redshift
Amazon Redshift

Apa itu Amazon Redshift Data API

Amazon Redshift Data API adalah layanan yang disediakan oleh Amazon Web Services (AWS) yang memungkinkan para pengembang untuk dengan lebih mudah dan aman menjalankan query SQL pada data warehouse Amazon Redshift mereka. Layanan ini menyederhanakan proses akses, pengambilan, dan pengeluaran data untuk bahasa pemrograman yang didukung oleh AWS SDK, seperti Python, Go, Java, Node.js, PHP, Ruby, dan C++.

Salah satu manfaat utama dari Redshift Data API adalah menghilangkan kebutuhan untuk kompleksitas manajemen koneksi database. Alih-alih melakukan koneksi JDBC atau ODBC, para pengembang dapat langsung menggunakan API untuk menjalankan perintah SQL.

Data API ini bersifat asinkron, yang berarti dapat digunakan untuk menjalankan query yang lebih lama tanpa mempertahankan koneksi terbuka. Hasil query disimpan selama 24 jam, dan dapat diekspor langsung ke dalam bucket S3. Selain itu, API ini juga terintegrasi dengan baik dengan AWS Secrets Manager dan AWS Identity and Access Management (IAM), sehingga menawarkan manajemen kredensial database yang lebih mudah dan aman.

Perintah

Berikut adalah beberapa contoh perintah yang tersedia dalam Amazon Redshift Data API.

Menjalankan Pernyataan 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"
}

Menjalankan Beberapa Pernyataan 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"
}

Membatalkan Query yang Sedang Berjalan

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

Mengambil Hasil dari Pernyataan 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
}

Mendeskripsikan Metadata tentang Pernyataan 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
}

Mendeskripsikan Sebuah Tabel

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"
    }
  ]
}

Menampilkan Metadata tentang Pernyataan 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
    }
  ]
}

Menampilkan Daftar Database dalam sebuah Cluster

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"]
}

Menampilkan Daftar Skema dalam sebuah Database

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"]
}

Menampilkan Daftar Tabel dalam sebuah Database

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"
    }
  ]
}

Referensi

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

Cara menggunakan AWS Cognito

Apa itu 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!