Use the copy_to_sql() function to create a table in Vantage based on a teradataml DataFrame or a pandas DataFrame.
The function takes a teradataml DataFrame or a pandas DataFrame and a table name as arguments, and generates DDL and DML commands 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 1: Create a new table in Vantage based on a pandas DataFrame
You must import pandas and copy_to_sql first.
>>>import pandas as pd >>>from teradataml.dataframe.copy_to import copy_to_sql
Assume you create the "sales" table as follows:
>>>sales = [{'accounts': 'Jones LLC', 'Jan': 150, 'Feb': 200.0, 'Mar': 140, 'datetime':'2017-04-01'}, {'accounts': 'Alpha Co', 'Jan': 200, 'Feb': 210.0, 'Mar': 215, 'datetime': '2017-04-01'}, {'accounts': 'Blue Inc', 'Jan': 50, 'Feb': 90.0, 'Mar': 95, 'datetime': '2017-04-01' }]
And a pandas DataFrame "pdf" is created from the table "sales", using command:
pdf = pd.DataFrame(sales)
Enter pdf to display the pandas DataFrame:
>>>pdf Feb Jan Mar accounts datetime 0 200.0 150 140 Jones LLC 2017-04-01 1 210.0 200 215 Alpha Co 2017-04-01 2 90.0 50 95 Blue Inc 2017-04-01
Use the copy_to_sql() function to create a new Vantage table "pandas_sales" based on the pandas DataFrame "pdf" that is created in the prerequisite.
>>>copy_to_sql(df = pdf, table_name = "pandas_sales", primary_index="accounts", if_exists="replace")
Verify that the new table "pandas_sales" exists in Vantage using the DataFrame() function which creates a DataFrame based on an existing table.
>>>df = DataFrame("pandas_sales")
>>> df Feb Jan Mar datetime accounts Jones LLC 200.0 150 140 2017-04-01 00:00:00.000000 Alpha Co 210.0 200 215 2017-04-01 00:00:00.000000 Blue Inc 90.0 50 95 2017-04-01 00:00:00.000000
Example 2: Use the optional index and index_label parameters, and use the index as Primary Index.
>>> copy_to_sql(df = pdf, table_name = "pandas_sales", index=True, index_label="idx", primary_index="idx", if_exists="replace")
>>> df = DataFrame("pandas_sales") >>> df Feb Jan Mar accounts datetime idx 0 200.0 150 140 Jones LLC 2017-04-01 00:00:00.000000 2 90.0 50 95 Blue Inc 2017-04-01 00:00:00.000000 1 210.0 200 215 Alpha Co 2017-04-01 00:00:00.000000
Example 3: Create a new table in Vantage based on a teradataml DataFrame
>>> df = DataFrame("sales") >>> df Feb Jan Mar Apr datetime accounts Blue Inc 90.0 50 95 101 04/01/2017 Alpha Co 210.0 200 215 250 04/01/2017 Jones LLC 200.0 150 140 180 04/01/2017 Yellow Inc 90.0 None None None 04/01/2017 Orange Inc 210.0 None None 250 04/01/2017 Red Inc 200.0 150 140 None 04/01/2017
>>> copy_to_sql(df = df, table_name = "sales_df1", primary_index="accounts", if_exists="replace")
>>> df1 = DataFrame("sales_df1") >>> df1 Feb Jan Mar Apr datetime accounts Blue Inc 90.0 50 95 101 17/01/04 Orange Inc 210.0 None None 250 17/01/04 Red Inc 200.0 150 140 None 17/01/04 Yellow Inc 90.0 None None None 17/01/04 Jones LLC 200.0 150 140 180 17/01/04 Alpha Co 210.0 200 215 250 17/01/04
Example 4: Create a new Primary Time Index Table in Vantage from a Pandas DataFrame
>>> copy_to_sql(df = pdf, table_name = "pandas_sales_pti", timecode_column="datetime", columns_list="accounts")
>>> pandas_sales_pti = DataFrame('pandas_sales_pti') >>> pandas_sales_pti TD_TIMECODE Feb Jan Mar accounts Jones LLC 2017-04-01 00:00:00.000000 200.0 150 140 Alpha Co 2017-04-01 00:00:00.000000 210.0 200 215 Blue Inc 2017-04-01 00:00:00.000000 90.0 50 95
See also to_sql() DataFrame Method.