阿里函数计算从数据湖查询数据到数据库中
import logging
import pymysql
def handler ( event, context) :
db = pymysql. connect(
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( cursor)
create_oss_text_table( cursor)
query_oss_text_table( cursor)
create_rds_mysql_schema( cursor)
create_rds_mysql_table( cursor)
insert_from_oss_to_rds_mysql( cursor)
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
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