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

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

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