Hyper API and AWS EC2

Question

Hi I am new to Tableau server and AWS EC2.

I am trying to write a python script to connect to AWS EC2 database, get the table out into .hyper file using hyper api from tableau and then push it into tableau server.

I had searched around but with no results. Currently am not sure how do I connect to the AWS EC2 database. So far I have only found an example on youtube that shows how to connect to MSAccess Database. https://www.youtube.com/watch?v=0-Hd35A3pN0

The github for hyper api examples does not have the syntax example I need for AWS EC2. https://github.com/tableau/hyper-api-samples

Hope that someone that knows how to do this can enlighten me somehow.

What do you mean by AWS EC2 database? Maybe you mean S3 or RDS? — shimo, Dec 03 '21 at 21:34
You want EC2 to get from redshift and push to tableau, right? If so, how about trying this official get-started, first from your local computer then move to EC2 or Lambda. https://help.tableau.com/current/api/hyper_api/en-us/index.html (I think fixing connection redshift to tableau is much easier, and you might face the same issue on EC2..) — shimo, Dec 04 '21 at 06:10

score 1 · Answer 1 · answered Dec 13 '21 at 23:23

You can start with psycopg2 to make your connection to redshift. This is completely separate from any Tableau function. After you have connected with psycopg2 you can run your sql and use pandas to create a csv file from the results. Then you will use the tableauhyperapi run_create_hyper_file_from_csv() function to create the actual hyper file. After this you will want to use tableauserverclient to publish to your server or Tableau Online.

redshift to pandas(csv)

 import psycopg2
 import pandas as pd

 REDSHIFT_USER = ''
 REDSHIFT_PASSWORD = ''
 REDSHIFT_HOST = ''
 REDSHIFT_PORT = ''
 REDSHIFT_DATABASE = ''

 cnxn = psycopg2.connect(f"postgresql://{REDSHIFT_USER}: 
 {REDSHIFT_PASSWORD}@{REDSHIFT_HOST}:{REDSHIFT_PORT}/{REDSHIFT_DATABASE}")


 sql = ''' <full sql statement> '''

 df = pd.read_sql(sql, cnxn)
 del cnxn
 #df.head()
 df.to_csv('yourFile.csv', index=False, encoding='utf-8', mode='w')

tableauhyperapi - csv to hyper: I use a version of this exact sample for creating a hyper file from a csv. https://github.com/tableau/hyper-api-samples/blob/main/Tableau-Supported/Python/create_hyper_file_from_csv.py

tableauserverclient for publishing: https://tableau.github.io/server-client-python/docs/api-ref#workbooks

This can all then be scheduled to run and publish without using any Tableau backgrounder resources. If you run into any issues with the hyperapi or serverclient setup let me know.

Hyper API and AWS EC2

1 Answers1