sqlglot.dialects.bigquery

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

152    class JSONPathTokenizer(jsonpath.JSONPathTokenizer):
153        VAR_TOKENS = {
154            *jsonpath.JSONPathTokenizer.VAR_TOKENS,
155            TokenType.DASH,
156            TokenType.NUMBER,
157        }

159    class Tokenizer(tokens.Tokenizer):
160        QUOTES = ["'", '"', '"""', "'''"]
161        COMMENTS = ["--", "#", ("/*", "*/")]
162        IDENTIFIERS = ["`"]
163        STRING_ESCAPES = ["\\"]
164
165        HEX_STRINGS = [("0x", ""), ("0X", "")]
166
167        BYTE_STRINGS = [(prefix + q, q) for q in t.cast(list[str], QUOTES) for prefix in ("b", "B")]
168
169        RAW_STRINGS = [(prefix + q, q) for q in t.cast(list[str], QUOTES) for prefix in ("r", "R")]
170
171        NESTED_COMMENTS = False
172
173        KEYWORDS = {
174            **tokens.Tokenizer.KEYWORDS,
175            "ANY TYPE": TokenType.VARIANT,
176            "BEGIN": TokenType.COMMAND,
177            "BEGIN TRANSACTION": TokenType.BEGIN,
178            "BYTEINT": TokenType.INT,
179            "BYTES": TokenType.BINARY,
180            "CURRENT_DATETIME": TokenType.CURRENT_DATETIME,
181            "DATETIME": TokenType.TIMESTAMP,
182            "DECLARE": TokenType.DECLARE,
183            "ELSEIF": TokenType.COMMAND,
184            "EXCEPTION": TokenType.COMMAND,
185            "EXPORT": TokenType.EXPORT,
186            "FLOAT64": TokenType.DOUBLE,
187            "FOR SYSTEM TIME": TokenType.TIMESTAMP_SNAPSHOT,
188            "FOR SYSTEM_TIME": TokenType.TIMESTAMP_SNAPSHOT,
189            "LOOP": TokenType.COMMAND,
190            "MODEL": TokenType.MODEL,
191            "NOT DETERMINISTIC": TokenType.VOLATILE,
192            "RECORD": TokenType.STRUCT,
193            "REPEAT": TokenType.COMMAND,
194            "TIMESTAMP": TokenType.TIMESTAMPTZ,
195            "WHILE": TokenType.COMMAND,
196        }
197        KEYWORDS.pop("DIV")
198        KEYWORDS.pop("VALUES")
199        KEYWORDS.pop("/*+")