import tableauserverclient as TSC try: from tableauhyperapi import HyperProcess, Connection, TableDefinition, SqlType, Telemetry, Inserter, CreateMode, TableName except ImportError: pass import pandas as pd import os from dotenv import load_dotenv load_dotenv() def __safely_getenv(var): """Checks if environment variable is defined before returning it""" if not os.getenv(var): raise EnvironmentError(f'Environment variable {var} not defined!') return os.getenv(var) def __get_auth(site): user = __safely_getenv('tableauUsername') password = __safely_getenv('tableauPassword') tableau_auth = TSC.TableauAuth(user, password, site_id=site) return tableau_auth def __login(site): global server try: server = TSC.Server(__safely_getenv('tableauServer'), use_server_version=True) except TSC.server.endpoint.exceptions.InternalServerError: print('Tableau server unresponsive, quitting.') return tableau_auth = __get_auth(site) return server.auth.sign_in(tableau_auth) def publish_hyper(filepath, datasource_id, site, mode='Overwrite', asjob=False): """ Publishes a locally saved hyper file on the Tableau server. Note: If you're not sure of the datasource ID, call get_datasource_ids. Args: filepath (str): The local path of the hyper file. datasource_id (str): The id of the datasource on the server. site (str): The name of the site on the Tableau server. mode (str, optional): The mode of publishing. Defaults to 'Overwrite'. asjob (bool, optional): Set as True for an asynchronous operation for big files. Defaults to False. Returns: datasource (tableauserverclient.server.DatasourceItem): The published datasource. Example: ``` filepath = 'data/my_hyperfile.hyper' datasource_id = 'your_datasource_id' site = 'finland' datasource = publish_hyper(filepath, datasource_id, site) ``` """ with __login(site): # get datasource datasource = server.datasources.get_by_id(datasource_id) # publish data source (specified in file_path) datasource = server.datasources.publish(datasource, filepath, mode, as_job=asjob) return datasource def delete_datasource(datasource_id, site, sure='No'): """ Deletes a data source on the Tableau server. Note: You need to explicitly pass sure='Yes', otherwise this will not work. Args: datasource_id (str): The id of the datasource on the server. site (str): The name of the site on the Tableau server. Returns: datasource (tableauserverclient.server.DatasourceItem): The published datasource. Example: ``` datasource_id = 'your_datasource_id' site = 'finland' delete_datasource(datasource_id, site) ``` """ if sure != 'Yes': print("This action is non-reversible. Please pass sure='Yes' if you are sure you want to proceed") return with __login(site): server.datasources.delete(datasource_id) def generate_hyper_schema(data_frame): """ Generates a hyperSchema object by trying to infer column data types from a pandas DataFrame. Args: data_frame (pd.DataFrame): The DataFrame from which to infer column data types. Returns: hyperschema (list): A list of TableDefinition.Column objects, representing the inferred schema. Example: ``` data = { 'report_date': pd.to_datetime(['2024-01-01', '2024-01-02']), 'isrc': ['USRC19401295', 'GBARL9300135'], 'country_code': ['US', 'FI'], 'num_streams': [100, 200], 'avg_stream_duration_seconds': [150.5, 200.75], 'is_local_track': [True, False], } df = pd.DataFrame(data) schema = generate_hyper_schema(df) ``` """ hyper_schema = [] for column_name, dtype in data_frame.dtypes.items(): if pd.api.types.is_datetime64_any_dtype(dtype): hyper_schema.append(TableDefinition.Column(column_name, SqlType.date())) print(f'Column: {column_name} inferred as date.') elif pd.api.types.is_bool_dtype(dtype): hyper_schema.append(TableDefinition.Column(column_name, SqlType.bool())) print(f'Column: {column_name} inferred as boolean.') elif pd.api.types.is_integer_dtype(dtype): hyper_schema.append(TableDefinition.Column(column_name, SqlType.big_int())) print(f'Column: {column_name} inferred as integer.') elif pd.api.types.is_float_dtype(dtype): hyper_schema.append(TableDefinition.Column(column_name, SqlType.double())) print(f'Column: {column_name} inferred as float/double.') else: hyper_schema.append(TableDefinition.Column(column_name, SqlType.text())) print(f'Column: {column_name} inferred as text.') return hyper_schema def write_hyper(data, hyper_path, hyperSchema=None, schemaName='Extract', tableName='Extract'): """ Writes a hyper file locally before publishing on the server. ???+ warning "Working with Alteryx" If somewhere down the road you also wish to work with the hyper using Alteryx, you need to use a specific naming convention: `schemaName='public', tableName='table1'` Args: data (pd.DataFrame): The data to be written to the hyper file. hyper_path (str): The local path where the hyper file will be stored. hyperSchema (list, optional): The Hyper Schema with column definitions. If not provided, it will be generated from the data. schemaName (str, optional): The name of the schema. Defaults to 'Extract'. If you want to work with the hyper using Alteryx, use 'public' as the schema name. tableName (str, optional): The name of the table. Defaults to 'Extract'. If you want to work with the hyper using Alteryx, use 'table1' as the schema name. Example: ``` data = { 'report_date': pd.to_datetime(['2024-01-01', '2024-01-02']), 'isrc': ['USRC19401295', 'GBARL9300135'], 'country_code': ['US', 'FI'], 'num_streams': [100, 200], 'avg_stream_duration_seconds': [150.5, 200.75], 'is_local_track': [True, False], } df = pd.DataFrame(data) hyper_path = "/path/to/store/hyper/file" hyperSchema = generate_hyper_schema(df) write_hyper(df, hyper_path, hyperSchema, 'public', 'table1') ``` """ if not isinstance(data, pd.DataFrame): raise TypeError('Unsupported data type, must be pandas dataframe') if not hyperSchema: hyperSchema = generate_hyper_schema(data) # Step 1: Start a new private local Hyper instance with HyperProcess(Telemetry.SEND_USAGE_DATA_TO_TABLEAU, 'hyperProcess' ) as hyper: # Step 2: Create the the .hyper file, replace it if it already exists with Connection(hyper.endpoint, hyper_path, CreateMode.CREATE_AND_REPLACE) as connection: # Step 3: Create the schema connection.catalog.create_schema(schemaName) # Step 4: Create the table definition schema = TableDefinition(table_name=TableName(schemaName,tableName), columns=hyperSchema) # Step 5: Create the table in the connection catalog connection.catalog.create_table(schema) with Inserter(connection, schema) as inserter: for _, row in data.iterrows(): inserter.add_row(row) inserter.execute() def refresh_extracts(datasource_ids:dict, site='finland'): """ Refreshes extract data sources on the Tableau server. Args: datasource_ids (dict): A dictionary containing the data source names and their corresponding IDs. Example: {'Datasource Name' : 'Datasource ID'} site (str, optional): The name of the site on the Tableau server. Defaults to 'finland'. Note: This function will print a message for each data source it attempts to refresh. Example: ```python data_source_dict = { 'name_of_data_source_1': 'id_of_data_source_1' 'name_of_data_source_2': 'id_of_data_source_2' } tableau.refresh_extracts(data_source_dict, site='finland') ``` """ if not isinstance(datasource_ids, dict): raise TypeError('Unsupported data type, must be dict') with __login(site): for name, id in datasource_ids.items(): print(f'Refreshing extract for {name} (ID: {id})') datasource = server.datasources.get_by_id(id) server.datasources.refresh(datasource) def get_project_ids(site, options=TSC.RequestOptions(pagesize=1000)): """ Gets the project names and IDs for a specified site. Args: site (str): The name of the site on the Tableau server. options (TSC.RequestOptions, optional): The request options for your API call. Defaults to defining only the number of pages to return. Returns: project_dict (dict): A dictionary containing the project names as keys and their corresponding IDs as values. Example: ``` project_dict = get_project_ids('finland') ``` """ with __login(site): all_project_items, _ = server.projects.get(req_options=options) project_dict = {proj.name : proj.id for proj in all_project_items} return project_dict def get_datasource_ids(site, options=TSC.RequestOptions(pagesize=1000)): """ Gets the datasource names and IDs for a specified site. Args: site (str): The name of the site on the Tableau server. options (TSC.RequestOptions, optional): The request options for your API call. Defaults to defining only the number of pages to return. Returns: datasource_dict (dict): A dictionary containing the datasource names as keys and their corresponding IDs as values. Example: ``` datasource_dict = get_datasource_ids('finland') ``` """ with __login(site): all_datasources, _ = server.datasources.get(req_options=options) datasource_dict = {ds.name : ds.id for ds in all_datasources} return datasource_dict def print_project_ids(site): """ Prints the project names and IDs for a specified site in a nice format. Args: site (str): The name of the site on the Tableau server. Prints: A formatted list of project names and their corresponding IDs. Example: ``` print_project_ids('finland') ``` """ print(f'Available Projects for site: {site}') ids = get_project_ids(site) for key, value in sorted(ids.items()): print(f'{key:.<80}{value:>36}') def print_datasource_ids(site): """ Prints the datasource names and IDs for a specified site in a nice format. Args: site (str): The name of the site on the Tableau server. Prints: A formatted list of datasource names and their corresponding IDs. Example: ``` print_datasource_ids('finland') ``` """ print(f'Available Datasources for site: {site}') ids = get_datasource_ids(site) for key, value in sorted(ids.items()): print(f'{key:.<80}{value:>36}') def new_datasource(site, project_id, filepath, asjob=False): """ Publishes a locally saved hyper file as a new datasource on the Tableau server. Args: site (str): The name of the site on the Tableau server. project_id (str): The ID of the project on the Tableau server where the datasource will be published. filepath (str): The local path to the hyper file to be published. asjob (bool, optional): Set as True for an asynchronous operation for big files. Defaults to False. Prints: A message indicating the successful publication of the datasource and its ID. Returns: id (str): the ID of the newly created datasource. Example: ``` id = new_datasource('finland', 'project_id', '/path/to/datasource.hyper') ``` """ with __login(site): new_datasource = TSC.DatasourceItem(project_id) new_datasource = server.datasources.publish(new_datasource, filepath, mode = TSC.Server.PublishMode.CreateNew, as_job=asjob) print(f"Datasource published. Datasource ID: {new_datasource.id}") id = new_datasource.id return id