Reverse engineer config yaml

[jkososki]

Any recommended approach for generating the config yaml from an existing database?

[littleK0i]

I have some "work-in-progress" code to perform such conversion. It is currently not documented, but you may run snowddl-convert --help to see list of arguments.

At this moment it tries to convert the following object types:

• DATABASE
• SCHEMA
• TABLE (with PRIMARY KEY, UNIQUE KEY, FOREIGN KEY constraints)
• VIEW
• SEQUENCE

Code of converters is available here: https://github.com/littleK0i/SnowDDL/tree/master/snowddl/converter

If I see some interest, I'll improve converters and include more object types.

Thank you!

[sindhuthirugnanam]

This is interesting. It would be very helpful if you have an option to choose which database and/or schema objects needs to be converted instead of scanning all databases in account associated with the input role.
For example, in a managed access Schema setup, the database and schema is owned by a more powerful role (like SYSADMIN) and the objects within in the schema is owned by Schema_FULL/Schema_OWNER role. For converting existing objects (like Table, View) of such schema, option to choose database and/or schema would be helpful without gaining elevated privileges

[littleK0i]

@sindhuthirugnanam , please check latest version 0.9.0.

I've added options --include-databases and --ignore-ownership for snowddl-convert entry-point. These options were implemented before, but not directly exposed.

Currently, there is no option to select specific schemas, but it should be a good start.

[sindhuthirugnanam]

Thank you for exposing these options. I'm trying to run the converter for a database and it seems to convert only table and sequence types within a database/schema. Views are being ignored. Tried with both default and --include-object-types option but had no luck in converting the view.

snowddl-convert --include-databases DB1 --ignore-ownership

snowddl-convert --include-databases DB1 --ignore-ownership --include-object-types table,view,sequence

Snowflake version = 6.32.0 (BUSINESS_CRITICAL), SnowDDL version = 0.9.6

Also, if you could expand the functionality to cover Functions it would be great.

[littleK0i]

@sindhuthirugnanam , convert for VIEWs was disabled in code. I've re-enabled it in 0.9.7, please give it a try.

Please note, active warehouse is required for VIEW converter, since it currently relies on GET_DDL() command and a regular expression.

FUNCTIONs converter is not easy to implement and maintain due to large variety of languages and options. It is definitely possible, but it will take a while.

[sindhuthirugnanam]

When I run the converter the tables are not formatted correctly. For example, if I have VARCHAR columns in a table, I expected the output will be:

columns:
  first_name:
    type: VARCHAR(50) 
    collate: en-ci

But instead, the converted returned output in following format:

columns:
  first_name:
    type: VARCHAR(50) COLLATE 'en-ci'

Is there any easy way to fix this in your code?

I also noticed the View definition are not formatted well either (note the regular expressions and additional ). Also the body of view is wrapped under "", instead of |-. Though the execution works fine with no issues, the formatting is not consistent with the docs.

columns:
  user_name: null
  warehouse_name: null
text: "(\n    SELECT \n            CURRENT_USER() AS user_name, \n            CURRENT_WAREHOUSE() AS warehouse_name\n\
  \ \n);

[littleK0i]

@sindhuthirugnanam , please check version 0.9.8, collate should now be properly supported.

For view text, I could not reproduce the problem. Here is the example of output I get:

columns:
  author_id: null
  author_name: null
  book_id: null
  book_name: null
text: |-
  SELECT a.author_id
      , a.author_name
      , b.book_id
      , b.book_name
  FROM table_007_01 a
      JOIN table_007_02 b ON (a.author_id=b.author_id);

And here is the place in code which explicitly instructs PyYAML to use |- syntax for view text: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/converter/view.py#L41

[sindhuthirugnanam]

Thank you for the fix on Table converter.

For View formatting issue, I was able to reproduce it consistently. I believe the formatting issue occurs when you read the GET_DDL output of the view that was created in a SQL way (from any IDE like VSCode, DBeaver, Snowflake Worksheet, etc...). However the View converter works perfectly fine if you reverse engineer a view that was created through SnowDDL Yaml format.

Could you follow below steps and check if you can reproduce the issue?

1. The view "get_session_details" is created using following code in Snowflake

CREATE OR REPLACE VIEW DBO.get_session_details AS 
(
 SELECT 
       CURRENT_USER() AS USER_NAME,
         CURRENT_WAREHOUSE() AS WH_NAME,
       CURRENT_DATABASE() AS DB_NAME
);

2. Run SnowDDL Converter and got following output in get_session_details.yaml

columns:
  user_name: null
  wh_name: null
  db_name: null
text: "(\n SELECT \n       CURRENT_USER() AS USER_NAME,\n         CURRENT_WAREHOUSE()\
  \ AS WH_NAME,\n       CURRENT_DATABASE() AS DB_NAME\n);"

[littleK0i]

@sindhuthirugnanam ,

The problem is related to extra "space" character you have after SELECT . Apparently, it is a known bug of PyYAML:

1. https://stackoverflow.com/questions/71836675/force-pyyaml-to-write-multiline-string-literals-regardless-of-string-content
2. Weird dumping when a literal block scalar contains trailing space yaml/pyyaml#411

I've added a basic improvement in version 0.9.9 to strip trailing spaces from each line of view text.

It should help to fix this issue and should not cause any unwanted side effects.