def download_infos(tablename, storename, keys):
o = ODPS("LTAIWt3hG5GvYBhX", "RriedkAIENmPvXvRmQcy9wRqOYx3QV", 'graph_embedding_intern_dev',
endpoint='http://service-corp.odps.aliyun-inc.com/api')
project = o.get_project()
csv_file = open(storename, mode='w')
writer = csv.writer(csv_file, delimiter='\t')
tunnel = TableTunnel(o)
download_session = tunnel.create_download_session(tablename)
with download_session.open_record_reader(0, download_session.count) as reader:
for record in reader:
info = [record[key] for key in keys]
writer.writerow(info)
print("complete storing {}".format(storename))
def uploadexcel(input_file,output_table_n='defult'):
odps = ODPS(ACCESS_KEY_ID,
ACCESS_KEY_SECRET,
PROJECT,
endpoint='http://service.odps.aliyun.com/api')
project = odps.get_project() # 取到默认项目
print(project)
ds = datetime.datetime.now().strftime('%Y%m%d')
print(ds)
wb = openpyxl.load_workbook(filename=input_file,read_only=True)
ws = wb.active
print(datetime.datetime.now())
output_table = odps.get_table(output_table_n)
if output_table.exist_partition('ds=' + ds):
output_table.delete_partition('ds=' + ds)
output_table.create_partition('ds=' + ds, if_not_exists=True)
tunnel = TableTunnel(odps)
upload_session = tunnel.create_upload_session(output_table.name, partition_spec='ds=' + ds)
print('output into', output_table_n, 'partition ds=', ds, ':\n', output_table.schema)
index=0
with upload_session.open_record_writer(0) as writer:
for row in ws.rows:
records = output_table.new_record()
i=0
for cell in row:
if cell is None:
records[i] = None
else:
records[i] = str(cell.value).encode('utf-8', "replace")
i=i+1
writer.write(records)
index=index+1
if index % 1000 == 0:
print(index)
upload_session.commit(0)
print('===========')
print(datetime.datetime.now())
# encoding=utf-8
from odps import ODPS
import pymysql
odps = ODPS('LTAI2wkz5kLt3205','RfTGzh2dfoBljs3ZZKwfpYw6OK9KYX','ofo')
project = odps.get_project()
print(project)
print(project.__getstate__())
t = odps.get_table('ofo_t_puser_partition')
with odps.execute_sql('select id from ofo_t_puser_partition where oauth in (1)').open_reader() as reader:
#返回结构化结果
#返回结果：
odps.Record {
id 90637
}
odps.Record {
id 90640
}
for record in reader:#返回结构化结果
print(record)
#返回原始sql结果
print(reader)
from odps import ODPS
import pymysql
from odps.models import partitions
o = ODPS('LTAIiLBfcRtW2Trt', 'wvQYMvfJknEpOoJorrEkB0bwUBZxf7', 'ofo_test')
project = o.get_project()
print(project)
with o.execute_sql("""select A.carno,
A.cname As Acname ,A.cvalue Acvalue,
B.cname As Bcname ,B.cvalue Bcvalue,
C.cname As Ccname, C.cvalue Ccvalue,
D.cname As Dcname ,D.cvalue Dcvalue,
E.cname As Ecname ,E.cvalue Ecvalue,
'location' As Qcname, location AS Qcvalue
from
(select carno, 'city' as cname, city as cvalue, area from ofo_t_bike_location where dt=20171221 ) A
left outer join
(select carno, 'order_num' as cname, 1 as cvalue from ofo_t_bike_location where dt=20171221
and TO_CHAR(locate_time, 'yyyymmdd') >= '20171214') B on A.carno = B.carno
left outer join
(select carno, 'area' as cname, area as cvalue from ofo_t_bike_location where dt=20171221
and TO_CHAR(locate_time, 'yyyymmdd') >= '20171214') D on A.carno = D.carno
left outer join
(select carno, 'lock_type' as cname, lock_type as cvalue from ofo_t_bike_location where dt=20171221
and TO_CHAR(locate_time, 'yyyymmdd') >= '20171214') E on A.carno = E.carno
left outer join
(select carno, concat_ws(';',cast(lng as string), cast(lat as string) ) As location from ofo_t_bike_location where dt=20171221) Q on A.carno = Q.carno
left outer join
(
select carno,'silent_tag' as cname, tag as cvalue from (
select t_last_day_silent.carno,
# -*- coding: utf-8 -*-
from odps import ODPS
from odps.df import DataFrame
from odps.types import Schema, Record
odps = ODPS('',
'PtMa1T01Nq0y2da8SBl0FRMmgxjE8X',
'GyyStatistical',
endpoint='https://service.odps.aliyun.com/api')
# 取到某个项目
project = odps.get_project('GyyStatistical')
# 取到默认项目
# project = odps.get_project()
# 列出项目下所有的表
print('----列出项目下所有的表 start----')
for table in odps.list_tables():
print(table)
print('----列出项目下所有的表 end----\n')
print('----同步方式 执行SQL语句 start----')
instance = odps.execute_sql('select * from ots_arealist1')
with instance.open_reader() as reader:
for record in reader:
print(record)
print(type(record))
print('----同步方式 执行SQL语句 end----\n')
# print('----异步方式 执行SQL语句 start----')
