Let's say I have to create tons of different SQL views. Having actual and target table DDLs, is it possible to programmatically automate view creation?

Example: I have 2 table DDLs:

CREATE TABLE target_ddl (
  invoice_sum INTEGER,
  date date DATE

CREATE TABLE actual_ddl (
  amount INTEGER,
  year INTEGER,
  months INTEGER,

What I need to do is to make actual_ddl data match the target_ddl schema, so obvious answer is view creation:

CREATE VIEW actual_ddl_as_target_ddl AS
SELECT amount AS invoice_sum, 
DATE(CONCAT_WS('-', year, months, day)) AS date FROM actual_ddl;

I wrote this one manually. Thought I feel like it's possible to write java/python library which will "create SQL views based on actual and target schema". What algorithms/best practices should I look into to achieve it?

  • And how would your program know how to create the view? Nov 29 '19 at 22:51
  • No the "actual DDL" contains insufficient information to establish how the tables should be trabsformed and/or joined. The naming convention in this case makes some suggestion to a human reader as to how the tables can be joined in a natural way, but that relies on a conceptual understanding of the code which the computer does not have (even the unfamiliar human reader can only speculate as to whether a simple join between the dates would be a correct use of the tables).
    – Steve
    Nov 29 '19 at 22:54
  • To be more specific: I have a user input: which columns have to be mapped to which. E.g: (year, month, day) to date, OR (datetime) to date. So there is a limited number of possible input column formats. It is definitely possible but requires a lot of wotk. I'm concerned about the complexity of algorithms and whether there are some patterns I might use. Nov 30 '19 at 7:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.