3

What approach should I follow to download DDL, DML and Stored Procedures from the teradata database using python.

I have created the sample code but what is the approach to download these sql files for data migration process.

udaExec = teradata.UdaExec(appName="HelloWorld", version="1.0",logConsole=False)
session = udaExec.connect(method="odbc", system="xxx",username="xxx", password="xxx");
for row in session.execute("show tables {} > {}".format(tables, export_tables)):
     print(row)

Unlike MSSQL which had mssql-scripter to download .sql files, does teradata provide any such option to download. Also, does it provide support to download sequences, views and procedures ?

For the Schema Migration process, what should be the best approach to download these files from the teradata as a source ?

starlord
  • 135
  • 9
  • Have you considered using [FastExport utiliity](https://docs.teradata.com/r/p~0sSD4zl4K8YPbEGnM3Rg/j4CJ07DIcrDMUC3i5SS3xg)? You can export to S3, HDFS, Kafka, local filesystem using their access modules. – Oluwafemi Sule Dec 13 '21 at 09:48
  • Databases don't store DML, unless you mean what's been logged? Still not sure that makes sense. For DDL, you can use the data dictionary views (tablesv, etc) to build show view/table/etc statements. – Andrew Dec 13 '21 at 14:41
  • 1
    For new development in Python, better to use supported `teradatasql` package not `teradata`. If stored procedures are compiled from Stored Procedure Language, storing the source within the database is default but optional. If stored, SHOW PROCEDURE will retrieve it. Otherwise, for SPL procedures as well as any written in Java or C/C++, SHOW will return only the CREATE PROCEDURE statement. BTW - you need to issue a separate SHOW for each object, and treat it as you would a SELECT - use a cursor and fetch the result set, – Fred Dec 13 '21 at 16:49
  • As an option : 1. Loop through DBC.TablesV 2. Run a `SHOW .` for each result 3. output the result set to a file – JNevill Dec 13 '21 at 17:17
  • @OluwafemiSule I didn't get it. Their seems to be no function for extracting DDL or DML .sql file. Based on the documentation, they are saying that files will be downloaded into .amj format, which is XML based format. Can you please let me know approach to directly download the files in .sql format just like how we download in mssql or netezza. – starlord Dec 14 '21 at 07:13
  • @Andrew actually for the schema migration process I need to download these DDL/DML files inorder to convert it into target database format. What approach should I follow to download the DDL, DML files from the teradata module for the next process. Really confused. – starlord Dec 14 '21 at 07:16
  • @Fred actually for the data migration process I need to download these DML/DDL files. The problem is their seems to be no direct approach to download these files. Any method/approach you know to download the files from Teradata to local system ? – starlord Dec 14 '21 at 07:19
  • @JNevill I'll try this approach but have done similar thing in the code and I didn't get useful outcome. I need to download the DML/DDL files from the teradata for the schema migration process. Any method to download these files just like how we download it from mssql-scripter or nz_ddl_tables using python ? – starlord Dec 14 '21 at 07:22
  • 1
    No. I don't believe there is any built-in functionality or python library to easy the burden. It's all about querying metadata in `DBC` and `SHOW` statements. If you are migrating away from Teradata, perhaps the platform you are migrating to can offer help? – JNevill Dec 14 '21 at 15:24
  • @JNevill actually I am migrating through python based code using dictionary mapping. The problem is how to store these DDL, DML files in the local system just like mssql-scripter or netezza scripter. I am stuck with this problem. Any solution to download these files to the local system via python ? – starlord Dec 15 '21 at 05:36
  • 1
    Query DBC.TablesV to build a list of object names / types. Execute SHOW statement for each object using a cursor to get DDL text as a resultset. Fetch the text from the cursor and write it to a file or files. As far as reverse-engineering all the rows into INSERT VALUES (what you seem to refer to as "extracting" DML), that is a lot more effort and the resulting files will be very much larger. Could be done but won't be very efficient; better to use some sort of "bulk" data transfer utility. – Fred Dec 15 '21 at 15:36
  • @Fred so as per my understanding I should use the show tables command and I should copy the text into .txt file right ? But the problem is the file would have text in one line and that would create issues during the conversion process to teradata using python I am mapping the keywords using regex based dictionary. Any alternative solution ? Can we download sql files from teradata using any command or scripter if possible ? – starlord Dec 17 '21 at 05:40
  • SHOW TABLE is the only way to generate/extract DDL text for a single table. It's possible someone has already written a script or tool that uses SHOW "under the covers" and made it publicly available, but I'm not aware of anything like that. For a conversion I would consider querying DBC metadata tables rather than text parsing / regex but that's just me. For extremely long object definitions there can be multiple rows returned by SHOW (split at fixed byte limit not at whitespace or delimiter), so write to the file as binary then read it as text (will have embedded newlines). – Fred Dec 17 '21 at 17:02
  • @Fred Any idea on how to get the views and procedures ? Is the process same ? Using show tables I am getting the output but I am afraid that may not be the same for views. – starlord Dec 23 '21 at 06:12
  • Yes, for views (TableKind=V) use SHOW VIEW. There is also SHOW PROCEDURE, if the procedure source was stored when the SP was compiled (default for SPL procedures.) – Fred Dec 24 '21 at 14:43

1 Answers1

0

Happy to share that I got the solution for this approach. In order to get the files in sql format use the given code to extract DDL and DML Code.

The given code is for sample database dbc.

with teradatasql.connect(host='enter_host_ip', user='---', password='---') as connect:
#get the table name and database name in csv file using select statement
df = pd.read_csv("result.csv", index_col=None)
for tables_name in df['TableName']:
    query = "SHOW TABLE DBC."+ tables_name
    try:
        df = pd.read_sql(query, connect)
        df1 = df['Request Text'][0]
        writePath = "C:\\Users\\SQL\\"+tables_name+".sql"
        with open(writePath, 'a') as f:
            dfAsString = df1
            f.write(dfAsString)
    except Exception as e1:
        print(tables_name)            
        pass

Note : Out of 192 tables I was able to get DDL/DML scripts for 189 tables. For tables perform manual intervention.

starlord
  • 135
  • 9