Google BigQuery Datatypes

[clairblacketer]

As noted by @kevinjyee in issue #168 and pull request #169 on the CommonDataModel page, datatypes varchar, int, and float are not supported by Google BigQuery. Instead STRING and INT64 should be used.

[t-abdul-basser]

@schuemie Aren't these translations already be covered--e.g varchar->string.

[schuemie]

Yes, those rules cover CASTs, but I think @clairblacketer is referring to CREATE TABLE statements. Currently something like CREATE TABLE concept (concept_id INT, concept_name VARCHAR(255)); is not translated properly.

We could add rules that blindly translate any occurrence of the types, like

bigquery,VARCHAR(@a),STRING
bigquery,INT,INT64
bigquery,FLOAT,FLOAT64

However, I'm not sure whether there would be side-effects. @kevinjyee, any thoughts?

[schuemie]

With the new BigQuery translation rules by @pavgra this SQL

create table concept (
  concept_id			    integer			  not null ,
  concept_name	            varchar(255)  not null ,
  domain_id		            varchar(20)		  not null ,
  vocabulary_id	            varchar(20)		  not null ,
  concept_class_id		   varchar(20)		  not null ,
  standard_concept		   varchar(1)		  null ,
  concept_code		   varchar(50)		  not null ,
  valid_start_date		   date			  not null ,
  valid_end_date		   date			  not null ,
  invalid_reason		   varchar(1)		  null
)
;

now translates to

create table concept (
  concept_id			    INT64			  not null ,
concept_name	            STRING  not null ,
domain_id		            STRING		  not null ,
vocabulary_id	            STRING		  not null ,
concept_class_id		   STRING		  not null ,
standard_concept		   STRING ,
concept_code		   STRING		  not null ,
valid_start_date		   date			  not null ,
valid_end_date		   date			  not null ,
invalid_reason		   STRING
)
;

for BigQuery.

Assuming this solves the issue, I'm closing it now. Let me know if it is still a problem.