阿里函数计算从数据湖查询数据到数据库中

阿里函数计算从数据湖查询数据到数据库中

# -*- coding: utf-8 -*-
import logging
import pymysql

# if you open the initializer feature, please implement the initializer function, as below:
# def initializer(context):
#  logger = logging.getLogger()
#  logger.info('Initializing Data Lake Analytics connection.')

# Data Lake Analytics SQL sample for https://yq.aliyun.com/articles/633513.

def handler(event, context):
  db = pymysql.connect(
    # Your Data Lake Analytics service endpoint on: https://datalakeanalytics.console.aliyun.com
    host='<dla_endpoint_info>.<region>.datalakeanalytics.aliyuncs.com',
    port=10000,
    user='<your_dla_username>',
    passwd='<your_dla_password>',
    database='',
    charset='utf8'
  )

  cursor = db.cursor()

  # Create OSS schema and table, which points to your OSS data.
  create_oss_schema(cursor)
  create_oss_text_table(cursor)

  # Query the OSS table for test.
  query_oss_text_table(cursor)

  # Create schema and table pointing to your RDS MySQL table.
  create_rds_mysql_schema(cursor)
  create_rds_mysql_table(cursor)

  # Insert from source OSS data into target RDS MySQL table.
  insert_from_oss_to_rds_mysql(cursor)

  # Query the target RDS MySQL via DLA.
  query_rds_mysql_table(cursor)

  db.close()

  return

def create_oss_schema(cursor):
  sql = 'CREATE SCHEMA IF NOT EXISTS oss_test_schema with DBPROPERTIES(' + \
        '  CATALOG = \'oss\',' + \
        '  LOCATION = \'oss://public-datasets-cn-hangzhou/tpch/1x/\'' + \
        ')'
  cursor.execute(sql)
  return

def create_oss_text_table(cursor):
  sql = 'CREATE EXTERNAL TABLE IF NOT EXISTS oss_test_schema.orders (' + \
        '  O_ORDERKEY INT,' + \
        '  O_CUSTKEY INT,' + \
        '  O_ORDERSTATUS STRING,' + \
        '  O_TOTALPRICE DOUBLE,' + \
        '  O_ORDERDATE DATE,' + \
        '  O_ORDERPRIORITY STRING,' + \
        '  O_CLERK STRING,' + \
        '  O_SHIPPRIORITY INT,' + \
        '  O_COMMENT STRING' + \
        ') ' + \
        'ROW FORMAT DELIMITED FIELDS TERMINATED BY \'|\' ' + \
        'STORED AS TEXTFILE ' + \
        'LOCATION \'oss://public-datasets-cn-hangzhou/tpch/1x/orders_text/\''
  cursor.execute(sql)
  return

def create_rds_mysql_schema(cursor):
  sql = 'CREATE SCHEMA `mysql_marketing` WITH DBPROPERTIES (' + \
        '  CATALOG = \'mysql\', ' + \
        '  LOCATION = \'jdbc:mysql://<rds_endpoint_info>.mysql.rds.aliyuncs.com:3306/marketing\', ' + \
        '  USER=\'<your_rds_instance_username>\', ' + \
        '  PASSWORD=\'<your_rds_instance_password>\', ' + \
        '  INSTANCE_ID = \'<your_rds_instance_id>\', ' + \
        '  VPC_ID = \'<your_vpc_id_where_your_rds_lives>\' ' + \
        ')'
  cursor.execute(sql)
  return

#
# Suppose you have already created a MySQL table in the RDS MySQL instance schema "marketing".
#
# create table top10_user (
#    custkey int,
#    order_cnt bigint
# );
#
def create_rds_mysql_table(cursor):
  sql = 'CREATE EXTERNAL TABLE IF NOT EXISTS mysql_marketing.top10_user (' + \
        '  custkey int, ' + \
        '  order_cnt bigint ' + \
        ') '
  cursor.execute(sql)
  return

def insert_from_oss_to_rds_mysql(cursor):
  sql = 'INSERT INTO mysql_marketing.top10_user (custkey, order_cnt) ' + \
        'SELECT o_custkey, count(*) AS cnt FROM oss_test_schema.orders ' + \
        'GROUP BY o_custkey ORDER BY cnt DESC LIMIT 10'
  cursor.execute(sql)
  return

def query_oss_text_table(cursor):
  sql='SELECT * FROM oss_test_schema.orders LIMIT 10'

  cursor.execute(sql)
  results = cursor.fetchall()
  print(results)
  return results

def query_rds_mysql_table(cursor):
  sql='SELECT * FROM mysql_marketing.top10_user LIMIT 10'

  cursor.execute(sql)
  results = cursor.fetchall()
  print(results)
  return results