getting an empty output for CREATE SCHEMA and OPTIONS about simple-ddl-parser HOT 23 CLOSED

Hi @xnuinside
Thanks for your support.

In addition to the above mentioned issue we are also facing below mentioned issues while converting Big Query DDL to json.

1. In Big Query DDL we have field level description, If we add any options inside field it is not converting. And below i have provided one of the field for your reference.
   Eg: calendar_dt DATE OPTIONS(description="Field Description")

2. If the cluster by field is not in brackets it is not converting. Below provided example for your reference.
   Eg: CLUSTER BY year_reporting_week_no

from simple-ddl-parser.

@kalyan939 @ankitdata I planned to add support for BigQuery, but did not do it yet. I will work on it as soon as I will be back from vacation on 8 November

from simple-ddl-parser.

@ankitdata , hi, thanks for reportng the issue, i'm on vacation till 8 November without laptop( so I can take a look on it and fix only after

from simple-ddl-parser.

@ankitdata of course! thanks for testing & reporting - it's really really helps make library better

from simple-ddl-parser.

@xnuinside I tested again with other ddl many issues resolved
Thanks for quick fix.

1. Now CREATE SCHEMA & CREATE TABLE is not returning in output json
2. And OPTIONS can be max 2 ? bcz if it is more than 2 then it is not returning in output json

please check and let me know.

Thank You.

from simple-ddl-parser.

@kalyan939 maybe problem in 2 dots, I think, a have tests only for dataset.table_name. I will check at the evening & will fix it

from simple-ddl-parser.

Hi @xnuinside,
Below i have mentioned my example query and output for your reference

Input DDL Query

CREATE SCHEMA IF NOT EXISTS project.calender
OPTIONS (
location="project-location"
);
CREATE TABLE project.calender.REF_CALENDAR (
calendar_dt DATE,
calendar_dt_id INT,
fiscal_half_year_reporting_week_no INT
)
OPTIONS (
description="Calendar table records reference list of calendar dates and related attributes used for reporting."
)
PARTITION BY DATETIME_TRUNC(fiscal_half_year_reporting_week_no, DAY)
CLUSTER BY calendar_dt

Output

[{
'columns': [{
'name': 'calendar_dt',
'type': 'DATE',
'size': None,
'references': None,
'unique': False,
'nullable': True,
'default': None,
'check': None
}, {
'name': 'calendar_dt_id',
'type': 'INT',
'size': None,
'references': None,
'unique': False,
'nullable': True,
'default': None,
'check': None
}, {
'name': 'fiscal_half_year_reporting_week_no',
'type': 'INT',
'size': None,
'references': None,
'unique': False,
'nullable': True,
'default': None,
'check': None
}],
'primary_key': [],
'alter': {},
'checks': [],
'index': [],
'partitioned_by': [],
'tablespace': None,
'table_name': 'REF_CALENDAR`',
'options': [{
'description': '"Calendar table records reference list of calendar dates and related attributes used for reporting."'
}],
'partition_by': {
'columns': ['fiscal_half_year_reporting_week_no', 'DAY'],
'type': 'DATETIME_TRUNC'
},
'cluster_by': ['calendar_dt'],
'dataset': None
}]

from simple-ddl-parser.

@ankitdata aaaa multiple options, I thought them all must be only in one OPTION() but inside can be multiple values. So it can be exactly multiple OPTIONS() okay. Got it, thanks! Will add support for that.

about schemas & names - I will continue work on it, looks like I did not cover some test cases.

@ankitdata one more big big thank for testing & feedback

from simple-ddl-parser.

@ankitdata, can you provide some more details just for my knowledge) I tried to google in that dialect you can create a schema with options, but failed. Can you send some link for me about it? Thank you!

from simple-ddl-parser.

okay, I found it - it's BigQuery, I will add support

from simple-ddl-parser.

Yes it's BigQuery ddl.
I Notice for OPTIONS it's showing distkey after converting to json
"distkey": "location=\"path\"
here I kept only one OPTIONS in ddl to check my converted json

Also I would like to know
in OPTIONS is it supports blank spaces
for eg-

OPTIONS (
  description="foo foo foo foo."
  );

for me it doesn't work if there is space in between foo foo
If you can add support to pass above ddl file would be great.

Thanks for coming early to support @xnuinside .

from simple-ddl-parser.

Hi @xnuinside
Is there any update on BigQuery support adding to simple-ddl-parser.

from simple-ddl-parser.

@ankitdata , hi! I started work on it in yesterday release https://github.com/xnuinside/simple-ddl-parser/releases/tag/v0.22.0, I fixed some things, for example, strings now parsed correctly - does not matter how much spaces inside (like in your example - OPTIONS (
description="Calendar table records reference list of calendar dates and related attributes used for reporting."
);) and OPTIONS statement in CREATE SCHEMA now works. But I still need to add support for OPTIONS in create table, in column and etc. Will continue work on it this week.

from simple-ddl-parser.

Hi @xnuinside ,
I checked v0.22.0 it is working fine with different DDL's as well.

CLUSTER BY issue I noticed > it's not working if it is not in brackets.

• If it is eg- CLUSTER BY __id it wont work
• If it is in brackets eg- CLUSTER BY (__id) then only it is working.

could you please include this as well in you next release.

Thanks .!

from simple-ddl-parser.

@ankitdata @kalyan939, I released 0.22.1 https://pypi.org/project/simple-ddl-parser/ with fix for Cluster by & OPTIONS in column . Please check. Tests added - https://github.com/xnuinside/simple-ddl-parser/blob/main/tests/test_bigquery.py#L242 . If there any more issues? or we can close the issue?

from simple-ddl-parser.

@ankitdata can you show sample for the 1st point because all tests passed - what mean ‘not returning?’

from simple-ddl-parser.

@xnuinside , In the output json file we are not able to get schema/project_id and dataset name
Eg: project_id.dataset.table_name
Output is missing project_id and dataset name

from simple-ddl-parser.

@kalyan939 thanks, samples very useful

from simple-ddl-parser.

@ankitdata I added test for multiple OPTIONS it works on version 0.22.1 as expected, maybe problem in OPTION value, maybe it has some specific format? can you show an OPTIONS sample that are not parsed validly? test here https://github.com/xnuinside/simple-ddl-parser/pull/96/files

from simple-ddl-parser.

@kalyan939 @ankitdata I released 0.22.3 version. Check please, I added bunch of tests: https://github.com/xnuinside/simple-ddl-parser/blob/main/tests/test_bigquery.py#L667, https://github.com/xnuinside/simple-ddl-parser/blob/main/tests/test_bigquery.py#L577, https://github.com/xnuinside/simple-ddl-parser/blob/main/tests/test_bigquery.py#L229 and etc

from simple-ddl-parser.

@xnuinside
I tested on latest 0.22.3 version.
few things I notice

1. In CREATE TABLE
   if my.data-abc-foo then in this case whatever after dot is I am getting in json
   out put = "schema":"data-abc-foo" _here my. is missing.
2. In CREATE SCHEMA IF NOT EXISTS
   out put = "schema":"my" that is showing as schema which is coming from table name I checked
   also please could you check OPTIONS once

eg- DDL

CREATE SCHEMA IF NOT EXISTS `my.data-cdh-hub`

CREATE TABLE `my.data-cdh-hub-REF-CALENDAR` (
  calendar_dt DATE,
  calendar_dt_id INT,
  fiscal_half_year_day_no INT,
  fiscal_half_year_end_dt DATE,
  fiscal_half_year_month_no INT,
  fiscal_half_year_nm STRING,
  fiscal_half_year_no INT,
  fiscal_half_year_reporting_week_no INT,
  fiscal_half_year_start_dt DATE,
  fiscal_half_year_week_no INT,
  fiscal_year_day_no INT,
  fiscal_year_end_dt DATE,
  fiscal_year_long_nm STRING,
  fiscal_year_month_no INT,
  fiscal_year_nm STRING,
  fiscal_year_no INT,
  fiscal_year_quarter_no INT,
  fiscal_year_reporting_week_no INT,
  fiscal_year_start_dt DATE,
  fiscal_year_week_no INT,
  month_day_no INT,
  month_end_dt DATE,
  month_id STRING,
  month_nm STRING,
  month_reporting_week_no INT,
  month_start_dt DATE,
  month_week_no INT,
  quarter_day_no INT,
  quarter_end_dt DATE,
  quarter_month_no INT,
  quarter_reporting_week_no INT,
  quarter_start_dt DATE,
  quarter_week_no INT,
  reporting_week_day_no INT,
  reporting_week_end_dt DATE,
  week_day_nm STRING,
  week_day_no INT,
  week_day_short_nm STRING,
  weekend_ind INT,
  weekend_ind_desc STRING,
  year_day_no INT,
  year_month_no INT,
  year_no INT,
  year_quarter_no STRING,
  year_reporting_week_no INT,
  year_week_no INT
  )
OPTIONS (
    location="location"
    )
OPTIONS (
  description="Calendar table records reference list of calendar dates and related attributes used for reporting."
  );

3. This is I added in DDL to try multiple OPTIONS -

OPTIONS (
    name ="path"
)
OPTIONS (
    kms_two="path",
    two="two two"
)
OPTIONS (
    kms_three="path",
    three="three",
    threethree="three three"
)
OPTIONS (
    kms_four="path",
    four="four four",
    fourin="four four four",
    fourlast="four four four four"
);

from simple-ddl-parser.

@ankitdata I released 0.22.4 https://github.com/xnuinside/simple-ddl-parser/blob/main/tests/test_bigquery.py#L755 - test for multiple OPTIONS statement & test for project_id in schema name - https://github.com/xnuinside/simple-ddl-parser/blob/main/tests/test_simple_ddl_parser.py#L2326

from simple-ddl-parser.

@ankitdata I'm closing this issue. If you will find anything new - feel free to open the new issue and thank you!

from simple-ddl-parser.