Table of Contents

• dbhelper
• vertica
  • create_table_with_query
  • create_table_from
  • create_table_local_temp
  • get_ddl
  • copy_to_vertica
  • merge_to_table
  • table_check
  • drop_table

vertica

create_table_with_query

def create_table_with_query(vertica_connection: VerticaConnection,
                            query: str,
                            to_table: str,
                            is_temp: bool = False) -> str

Create Table in Vertica with SQL Query

Arguments:

• vertica_connection VerticaConnection - Vertica Connection
• query str - SQL Query (SELECT Only)

• to_table str - Table name ([schema.tablename

  tablename])

• is_temp_table bool, optional - create temp table delete auto when Vertica Session Connect closed. Defaults to False.

Raises:

• Exception - Create Table Error

create_table_from

def create_table_from(vertica_connection: VerticaConnection, from_table: str,
                      to_table: str)

Create Table from another table in vertica database

Arguments:

• vertica_connection VerticaConnection - Vertica Connection
• from_table str - Source table copy DDL.
• to_table str - Target table name

Raises:

• Exception - Execute Create Table Error

create_table_local_temp

def create_table_local_temp(vertica_connection: VerticaConnection, query: str,
                            to_tablename: str)

Create local temp table in vertica

Arguments:

• vertica_connection VerticaConnection - Vertica Connection
• query str - SQL Statement (SELECT Only).
• to_tablename str - Target table name only (No Schema name)

Raises:

• Exception - Execute Create Table Error

get_ddl

def get_ddl(vertica_connection: VerticaConnection, query: str,
            to_table: str) -> str

Get SQL Create Table Statement With Query

Arguments:

• vertica_connection VerticaConnection - Vertica Connection
• query str - query (str): SQL Statement (SELECT Only).
• to_table str - Target table name (Full call: ‘schema.table’ )

Returns:

• str - SQL Create table

copy_to_vertica

def copy_to_vertica(vertica_connection: VerticaConnection,
                    fs: Union[os.PathLike, io.BytesIO, io.StringIO, Any],
                    table: str,
                    columns: List[str],
                    comprassion: str = "",
                    reject_table: str = None)

summary

Arguments:

• vertica_connection VerticaConnection - Vertica Connection .
• fs Union[os.PathLike, io.BytesIO, io.StringIO] - file path or file open. Example: open(“/tmp/file.csv”, “rb”)
• table str - Target table name.
• comprassion str - Specifies the input format. [UNCOMPRESSED (default), BZIP,GZIP,LZO,ZSTD]
• reject_table str, optional - Reject Data to table name. Defaults to None.
• check_column bool, optional - Check column if exists. Defaults to True.

Raises:

• Exception - Target table copy is not exist.
• Exception - Copy data error.

Returns:

• str - SQL COPY

merge_to_table

def merge_to_table(
        vertica_connection: VerticaConnection,
        from_table: str,
        to_table: str,
        merge_on_columns: List[str],
        *,
        no_execute: bool = False,
        add_field_insert: Dict[str, AnyStr] = None,
        add_field_update: Dict[str, AnyStr] = None) -> Union[AnyStr, int]

Vertica Merge Data between table and table

Arguments:

• vertica_connection VerticaConnection - Vertica Connection.
• from_table str - Source table name.
• to_table str - Target table name.
• merge_on_columns List[str] - Check columns match is UPDATE and not match is INSERT
• no_execute bool, optional - If True Return SQL Statement Only. Defaults to False.
• add_field_insert Dict[str, AnyStr], optional - Add field insret more. Defaults to None.
• add_field_update Dict[str, AnyStr], optional - Add field update more. Defaults to None.

Raises:

• Exception - Error Merge is not success.

Returns:

Union[AnyStr, int]: Return if no_execute == True Return SQL Statement else Return merge_total count total data merge into table target.

table_check

def table_check(vertica_connection: VerticaConnection,
                table: str) -> pd.DataFrame

Check table if exists

Arguments:

• vertica_connection VerticaConnection - Vertica Connection.
• table str - Full Table name.

Returns:

• pd.DataFrame - table if exsit return Padas DataFrame.

drop_table

def drop_table(vertica_connection: VerticaConnection, table: str) -> bool

Drop Table if exists.

Arguments:

• vertica_connection VerticaConnection - Vertica Connection.
• table str - Full Table name.

Returns:

• bool - is success.