Use the to_sql() function to create a table in Vantage based on a teradataml DataFrame.
The function takes a table name as an argument, and generates DDL and DML queries that creates a table in Vantage. You can also specify the name of the schema in the database to write the table to. If no schema name is specified, the default database schema is used.
Example: Create a table
df = DataFrame("sales")
Enter df to display the teradataml DataFrame:
>>> df Feb Jan Mar Apr datetime accounts Blue Inc 90.0 50.0 95.0 101.0 04/01/2017 Alpha Co 210.0 200.0 215.0 250.0 04/01/2017 Jones LLC 200.0 150.0 140.0 180.0 04/01/2017 Yellow Inc 90.0 NaN NaN NaN 04/01/2017 Orange Inc 210.0 NaN NaN 250.0 04/01/2017 Red Inc 200.0 150.0 140.0 NaN 04/01/2017
And a new teradataml DataFrame is created from this DataFrame with only the columns "accounts", "Jan", "Feb".
>>> df = df.select(["accounts", "Jan", "Feb"])
Enter df to display the new DataFrame:
>>> df accounts Jan Feb 0 Jones LLC 150.0 200.0 1 Blue Inc 50.0 90.0 2 Yellow Inc NaN 90.0 3 Orange Inc NaN 210.0 4 Alpha Co 200.0 210.0 5 Red Inc 150.0 200.0
Example: Create a NOPI table
Use the to_sql() function to create a new table "sales_df1" in Vantage based on the new DataFrame. The table "sales_df1" is a NOPI table because the primary index was not specified.
>>> df.to_sql("sales_df1")
Verify that the new table "sales_df1" exists in Vantage using the DataFrame() function which creates a DataFrame based on an existing table.
>>> df1 = DataFrame("sales_df1")
Enter df1 to display the DataFrame just created.
>>> df1 accounts Jan Feb 0 Blue Inc 50 90.0 1 Orange Inc None 210.0 2 Red Inc 150 200.0 3 Yellow Inc None 90.0 4 Jones LLC 150 200.0 5 Alpha Co 200 210.0
Example: Create a table using the optional parameter primary_index
Use the to_sql() function with the optional parameter "primary_index" to create a new table "sales_df2" in Vantage with the primary index "accounts". Use the optional parameter "if_exists" to replace the existing table "sales_df2" in Vantage
>>> df = DataFrame("sales") >>> df = df.select(["accounts", "datetime"])
>>> df.to_sql("sales_df2", if_exists="replace", primary_index="accounts")
>>> df2 = DataFrame("sales_df2") >>> df2 datetime accounts Blue Inc 17/01/04 Orange Inc 17/01/04 Red Inc 17/01/04 Yellow Inc 17/01/04 Jones LLC 17/01/04 Alpha Co 17/01/04
Example: Use optional parameter "set_table" to create a SET table "sales_subset_set".
>>> df = DataFrame("sales")
>>> # First create a new dataframe with the columns "Apr", "datetime", to showcase the effect of the paramter >>> df_for_set = df.select(['Apr','datetime']) >>> df_for_set Apr datetime 0 101 04/01/2017 1 250 04/01/2017 2 180 04/01/2017 3 None 04/01/2017 4 250 04/01/2017 5 None 04/01/2017
>>> df_for_set.to_sql('sales_subset_set', primary_index='Apr', set_table=True)
>>> set_df = DataFrame('sales_subset_set') >>> set_df datetime Apr 180 17/01/04 101 17/01/04 250 17/01/04 NaN 17/01/04
Example: Create a new Primary Time Index table "sales_pti".
>>> df.to_sql('sales_pti', timecode_column='datetime', columns_list='accounts')
>>> sales_pti= DataFrame('sales_pti') >>> sales_pti TD_TIMECODE Feb Jan Mar Apr accounts Blue Inc 17/01/04 90.0 50 95 101 Orange Inc 17/01/04 210.0 None None 250 Red Inc 17/01/04 200.0 150 140 None Yellow Inc 17/01/04 90.0 None None None Jones LLC 17/01/04 200.0 150 140 180 Alpha Co 17/01/04 210.0 200 215 250