sqlglot.dialects.bigquery

 25class BigQuery(Dialect):
 26    WEEK_OFFSET = -1
 27    UNNEST_COLUMN_ONLY = True
 28    SUPPORTS_USER_DEFINED_TYPES = False
 29    LOG_BASE_FIRST = False
 30    HEX_LOWERCASE = True
 31    FORCE_EARLY_ALIAS_REF_EXPANSION = True
 32    EXPAND_ONLY_GROUP_ALIAS_REF = True
 33    PRESERVE_ORIGINAL_NAMES = True
 34    HEX_STRING_IS_INTEGER_TYPE = True
 35    BYTE_STRING_IS_BYTES_TYPE = True
 36    UUID_IS_STRING_TYPE = True
 37    ANNOTATE_ALL_SCOPES = True
 38    PROJECTION_ALIASES_SHADOW_SOURCE_NAMES = True
 39    TABLES_REFERENCEABLE_AS_COLUMNS = True
 40    SUPPORTS_STRUCT_STAR_EXPANSION = True
 41    EXCLUDES_PSEUDOCOLUMNS_FROM_STAR = True
 42    QUERY_RESULTS_ARE_STRUCTS = True
 43    JSON_EXTRACT_SCALAR_SCALAR_ONLY = True
 44    JSON_PATH_SINGLE_DOT_IS_WILDCARD = True
 45    LEAST_GREATEST_IGNORES_NULLS = False
 46    DEFAULT_NULL_TYPE = exp.DType.BIGINT
 47    PRIORITIZE_NON_LITERAL_TYPES = True
 48    ALIAS_POST_VERSION = False
 49
 50    # https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#initcap
 51    INITCAP_DEFAULT_DELIMITER_CHARS = ' \t\n\r\f\v\\[\\](){}/|<>!?@"^#$&~_,.:;*%+\\-'
 52
 53    # https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#case_sensitivity
 54    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
 55    ASCII_ONLY_NORMALIZATION = True
 56
 57    # bigquery udfs are case sensitive
 58    NORMALIZE_FUNCTIONS = False
 59
 60    # https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time
 61    TIME_MAPPING = {
 62        "%x": "%m/%d/%y",
 63        "%D": "%m/%d/%y",
 64        "%E6S": "%S.%f",
 65        "%e": "%-d",
 66        "%F": "%Y-%m-%d",
 67        "%T": "%H:%M:%S",
 68        "%c": "%a %b %e %H:%M:%S %Y",
 69    }
 70
 71    INVERSE_TIME_MAPPING = {
 72        # Preserve %E6S instead of expanding to %T.%f - since both %E6S & %T.%f are semantically different in BigQuery
 73        # %E6S is semantically different from %T.%f: %E6S works as a single atomic specifier for seconds with microseconds, while %T.%f expands incorrectly and fails to parse.
 74        "%H:%M:%S.%f": "%H:%M:%E6S",
 75    }
 76
 77    FORMAT_MAPPING = {
 78        "dd": "%d",
 79        "DD": "%d",
 80        "mm": "%m",
 81        "MM": "%m",
 82        "mon": "%b",
 83        "MON": "%b",
 84        "month": "%B",
 85        "MONTH": "%B",
 86        "yyyy": "%Y",
 87        "YYYY": "%Y",
 88        "yy": "%y",
 89        "YY": "%y",
 90        "HH": "%I",
 91        "HH12": "%I",
 92        "hh24": "%H",
 93        "HH24": "%H",
 94        "mi": "%M",
 95        "MI": "%M",
 96        "ss": "%S",
 97        "SS": "%S",
 98        "SSSSS": "%f",
 99        "tzh": "%z",
100        "TZH": "%z",
101    }
102
103    # The _PARTITIONTIME and _PARTITIONDATE pseudo-columns are not returned by a SELECT * statement
104    # https://cloud.google.com/bigquery/docs/querying-partitioned-tables#query_an_ingestion-time_partitioned_table
105    # https://cloud.google.com/bigquery/docs/querying-wildcard-tables#scanning_a_range_of_tables_using_table_suffix
106    # https://cloud.google.com/bigquery/docs/query-cloud-storage-data#query_the_file_name_pseudo-column
107    PSEUDOCOLUMNS = {
108        "_PARTITIONTIME",
109        "_PARTITIONDATE",
110        "_TABLE_SUFFIX",
111        "_FILE_NAME",
112        "_DBT_MAX_PARTITION",
113    }
114
115    # All set operations require either a DISTINCT or ALL specifier
116    SET_OP_DISTINCT_BY_DEFAULT = dict.fromkeys((exp.Except, exp.Intersect, exp.Union), None)
117
118    # https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#percentile_cont
119    COERCES_TO = {
120        **TypeAnnotator.COERCES_TO,
121        exp.DType.BIGDECIMAL: {exp.DType.DOUBLE},
122    }
123    COERCES_TO[exp.DType.DECIMAL] |= {exp.DType.BIGDECIMAL}
124    COERCES_TO[exp.DType.BIGINT] |= {exp.DType.BIGDECIMAL}
125    COERCES_TO[exp.DType.VARCHAR] |= {
126        exp.DType.DATE,
127        exp.DType.DATETIME,
128        exp.DType.TIME,
129        exp.DType.TIMESTAMP,
130        exp.DType.TIMESTAMPTZ,
131    }
132
133    EXPRESSION_METADATA = EXPRESSION_METADATA.copy()
134
135    def normalize_identifier(self, expression: E) -> E:
136        if (
137            isinstance(expression, exp.Identifier)
138            and self.normalization_strategy is NormalizationStrategy.CASE_INSENSITIVE
139        ):
140            parent = expression.parent
141            while isinstance(parent, exp.Dot):
142                parent = parent.parent
143
144            # In BigQuery, CTEs are case-insensitive, but UDF and table names are case-sensitive
145            # by default. The following check uses a heuristic to detect tables based on whether
146            # they are qualified. This should generally be correct, because tables in BigQuery
147            # must be qualified with at least a dataset, unless @@dataset_id is set.
148            case_sensitive = (
149                isinstance(parent, exp.UserDefinedFunction)
150                or (
151                    isinstance(parent, exp.Table)
152                    and parent.db
153                    and (parent.meta_get("quoted_table") or not parent.meta_get("maybe_column"))
154                )
155                or expression.meta_get("is_table")
156            )
157            if not case_sensitive:
158                expression.set("this", expression.this.translate(ASCII_LOWER))
159
160            return t.cast(E, expression)
161
162        return super().normalize_identifier(expression)
163
164    class JSONPathTokenizer(jsonpath.JSONPathTokenizer):
165        VAR_TOKENS = {
166            *jsonpath.JSONPathTokenizer.VAR_TOKENS,
167            TokenType.DASH,
168            TokenType.NUMBER,
169        }
170
171    class Tokenizer(tokens.Tokenizer):
172        QUOTES = ["'", '"', '"""', "'''"]
173        COMMENTS = ["--", "#", ("/*", "*/")]
174        IDENTIFIERS = ["`"]
175        STRING_ESCAPES = ["\\"]
176
177        HEX_STRINGS = [("0x", ""), ("0X", "")]
178
179        BYTE_STRINGS = [(prefix + q, q) for q in t.cast(list[str], QUOTES) for prefix in ("b", "B")]
180
181        RAW_STRINGS = [(prefix + q, q) for q in t.cast(list[str], QUOTES) for prefix in ("r", "R")]
182
183        NESTED_COMMENTS = False
184
185        KEYWORDS = {
186            **tokens.Tokenizer.KEYWORDS,
187            "ANY TYPE": TokenType.VARIANT,
188            "BEGIN": TokenType.COMMAND,
189            "BEGIN TRANSACTION": TokenType.BEGIN,
190            "BYTEINT": TokenType.INT,
191            "BYTES": TokenType.BINARY,
192            "CURRENT_DATETIME": TokenType.CURRENT_DATETIME,
193            "DATETIME": TokenType.TIMESTAMP,
194            "DECLARE": TokenType.DECLARE,
195            "ELSEIF": TokenType.COMMAND,
196            "EXCEPTION": TokenType.COMMAND,
197            "EXPORT": TokenType.EXPORT,
198            "FLOAT64": TokenType.DOUBLE,
199            "LOOP": TokenType.COMMAND,
200            "MODEL": TokenType.MODEL,
201            "RECORD": TokenType.STRUCT,
202            "REPEAT": TokenType.COMMAND,
203            "TIMESTAMP": TokenType.TIMESTAMPTZ,
204            "WHILE": TokenType.COMMAND,
205        }
206        KEYWORDS.pop("DIV")
207        KEYWORDS.pop("VALUES")
208        KEYWORDS.pop("/*+")
209
210    Parser = BigQueryParser
211
212    Generator = BigQueryGenerator

164    class JSONPathTokenizer(jsonpath.JSONPathTokenizer):
165        VAR_TOKENS = {
166            *jsonpath.JSONPathTokenizer.VAR_TOKENS,
167            TokenType.DASH,
168            TokenType.NUMBER,
169        }

171    class Tokenizer(tokens.Tokenizer):
172        QUOTES = ["'", '"', '"""', "'''"]
173        COMMENTS = ["--", "#", ("/*", "*/")]
174        IDENTIFIERS = ["`"]
175        STRING_ESCAPES = ["\\"]
176
177        HEX_STRINGS = [("0x", ""), ("0X", "")]
178
179        BYTE_STRINGS = [(prefix + q, q) for q in t.cast(list[str], QUOTES) for prefix in ("b", "B")]
180
181        RAW_STRINGS = [(prefix + q, q) for q in t.cast(list[str], QUOTES) for prefix in ("r", "R")]
182
183        NESTED_COMMENTS = False
184
185        KEYWORDS = {
186            **tokens.Tokenizer.KEYWORDS,
187            "ANY TYPE": TokenType.VARIANT,
188            "BEGIN": TokenType.COMMAND,
189            "BEGIN TRANSACTION": TokenType.BEGIN,
190            "BYTEINT": TokenType.INT,
191            "BYTES": TokenType.BINARY,
192            "CURRENT_DATETIME": TokenType.CURRENT_DATETIME,
193            "DATETIME": TokenType.TIMESTAMP,
194            "DECLARE": TokenType.DECLARE,
195            "ELSEIF": TokenType.COMMAND,
196            "EXCEPTION": TokenType.COMMAND,
197            "EXPORT": TokenType.EXPORT,
198            "FLOAT64": TokenType.DOUBLE,
199            "LOOP": TokenType.COMMAND,
200            "MODEL": TokenType.MODEL,
201            "RECORD": TokenType.STRUCT,
202            "REPEAT": TokenType.COMMAND,
203            "TIMESTAMP": TokenType.TIMESTAMPTZ,
204            "WHILE": TokenType.COMMAND,
205        }
206        KEYWORDS.pop("DIV")
207        KEYWORDS.pop("VALUES")
208        KEYWORDS.pop("/*+")