[Tableau(태블로)]Python으로 서버에 게시된 데이터를 Dataframe으로 추출하기(download or export hyper to csv,dataframe) -추가 자료

 

 

이전에 Python으로 태블로 서버의 hyper를 dataframe으로 추출하여 전처리 및 다시 서버로 업로드 하는 코드를 작성했었다.

https://mrnoobiest.tistory.com/132

[Tableau(태블로)]Python으로 서버에 게시된 데이터를 Dataframe으로 추출하기(download or export hyper to csv,d

 

 

 

코드 중간부분의 쿼리문을 일일히 작성하기 어려울때 처리하는 방법을 추가한다.

 

---

이전 버전 코드

from tableauhyperapi import HyperProcess, Connection, TableDefinition, SqlType, Telemetry, Inserter, CreateMode, TableName
from tableauhyperapi import escape_string_literal


PATH_TO_HYPER ='/File_Path_Location/Tableau_Datasource_File_Name.hyper'


#CSV를 Hyper로 변환후 서버로 던질준비
with HyperProcess(Telemetry.SEND_USAGE_DATA_TO_TABLEAU, 'myapp' ) as hyper:

    with Connection(endpoint=hyper.endpoint, 
                    create_mode=CreateMode.CREATE_AND_REPLACE,
                    database=PATH_TO_HYPER) as connection:

        connection.catalog.create_schema('Extract')

        schema = TableDefinition(table_name=TableName('Extract','Extract'),
            columns=[
                    TableDefinition.Column('컬럼명1', SqlType.text()),
                    TableDefinition.Column('컬럼명2', SqlType.date()), 
                    TableDefinition.Column('컬럼명3', SqlType.text()),
                    TableDefinition.Column('컬럼명4', SqlType.int()),
                    TableDefinition.Column('컬럼명5', SqlType.double()),                              
     ])
        connection.catalog.create_table(schema)
        insert_csv_data = connection.execute_command(
            command=f"COPY {schema.table_name} FROM {escape_string_literal(PATH_TO_CSV)} WITH "
             f"(format csv,NULL 'NaN',delimiter ',', header)"



        )

#데이터 원본을 저장할 프로젝트 ID를 검색
with server.auth.sign_in(tableau_auth):
    all_project = list(TSC.Pager(server.projects, req_option))
    project='Projects_Name' 

    project_id = [i.id for i in all_project if i.name==project]
    project_name = [i.name for i in all_project if i.name==project]
    print(project_id)
    print(project_name)


#위의 데이터를 전송
try:
    # Published Hyper to Tableau Server    
    conn = TableauServerConnection(config_json=TS_CONFIG, env='my_env')
    conn.sign_in()
    response= conn.publish_data_source(
        datasource_file_path=PATH_TO_HYPER
        ,datasource_name='Datasource_Name'
        ,project_id='project_id')
    conn.sign_out()
except:
    pass

 

 

 

 

---

 

 

 

개선된 버전

from tableauhyperapi import CreateMode, escape_string_literal, Inserter, Telemetry, TableName, HyperProcess, Connection, TableDefinition, SqlType



PATH_TO_CSV='./경로/데이터 원본.csv'
PATH_TO_HYPER='./경로/변환된 hyper의 위치.hyper'
new_df=pd.read_csv(PATH_TO_CSV)


columns = []
for column_name, column_type in zip(new_df.columns, new_df.dtypes):
    if column_name in ['실수 형태로 저장하고픈 컬럼들','실수 컬럼2']:
        sql_type = SqlType.double()
    elif column_name in ['시간 형태로 저장하고픈 컬럼들','시간 컬럼2']:
        sql_type = SqlType.timestamp()
    else:
        sql_type = SqlType.text()
    columns.append(TableDefinition.Column(column_name, sql_type))



with HyperProcess(Telemetry.SEND_USAGE_DATA_TO_TABLEAU, 'myapp') as hyper:
    with Connection(endpoint=hyper.endpoint, database=PATH_TO_HYPER) as connection:
        table_name = TableName('Extract', 'Extract')
        table_def = TableDefinition(table_name, columns)
        schema = TableDefinition(table_name=TableName('Extract','Extract'),
            columns=columns)
        connection.catalog.create_table(schema)
        insert_csv_data = connection.execute_command(
            command=f"COPY {schema.table_name} FROM {escape_string_literal(PATH_TO_CSV)} WITH "
             f"(format csv,NULL 'NaN',delimiter ',', header)"

try:
    # Published Hyper to Tableau Server    
    conn = TableauServerConnection(config_json=TS_CONFIG, env='my_env')
    conn.sign_in()
    response= conn.publish_data_source(
        datasource_file_path=PATH_TO_HYPER
        ,datasource_name='{}_Database'.format(database_name)
        ,project_id='데이터 원본을 저장할 프로젝트 ID')
    conn.sign_out()
except Exception as e:
    print(e)