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        "DD": "%d",
 78        "mm": "%m",
 79        "MM": "%m",
 80        "mon": "%b",
 81        "MON": "%b",
 82        "month": "%B",
 83        "MONTH": "%B",
 84        "yyyy": "%Y",
 85        "YYYY": "%Y",
 86        "yy": "%y",
 87        "YY": "%y",
 88        "HH": "%I",
 89        "HH12": "%I",
 90        "hh24": "%H",
 91        "HH24": "%H",
 92        "mi": "%M",
 93        "MI": "%M",
 94        "ss": "%S",
 95        "SS": "%S",
 96        "SSSSS": "%f",
 97        "tzh": "%z",
 98        "TZH": "%z",
 99    }
100
101    # The _PARTITIONTIME and _PARTITIONDATE pseudo-columns are not returned by a SELECT * statement
102    # https://cloud.google.com/bigquery/docs/querying-partitioned-tables#query_an_ingestion-time_partitioned_table
103    # https://cloud.google.com/bigquery/docs/querying-wildcard-tables#scanning_a_range_of_tables_using_table_suffix
104    # https://cloud.google.com/bigquery/docs/query-cloud-storage-data#query_the_file_name_pseudo-column
105    PSEUDOCOLUMNS = {
106        "_PARTITIONTIME",
107        "_PARTITIONDATE",
108        "_TABLE_SUFFIX",
109        "_FILE_NAME",
110        "_DBT_MAX_PARTITION",
111    }
112
113    # All set operations require either a DISTINCT or ALL specifier
114    SET_OP_DISTINCT_BY_DEFAULT = dict.fromkeys((exp.Except, exp.Intersect, exp.Union), None)
115
116    # https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#percentile_cont
117    COERCES_TO = {
118        **TypeAnnotator.COERCES_TO,
119        exp.DType.BIGDECIMAL: {exp.DType.DOUBLE},
120    }
121    COERCES_TO[exp.DType.DECIMAL] |= {exp.DType.BIGDECIMAL}
122    COERCES_TO[exp.DType.BIGINT] |= {exp.DType.BIGDECIMAL}
123    COERCES_TO[exp.DType.VARCHAR] |= {
124        exp.DType.DATE,
125        exp.DType.DATETIME,
126        exp.DType.TIME,
127        exp.DType.TIMESTAMP,
128        exp.DType.TIMESTAMPTZ,
129    }
130
131    EXPRESSION_METADATA = EXPRESSION_METADATA.copy()
132
133    def normalize_identifier(self, expression: E) -> E:
134        if (
135            isinstance(expression, exp.Identifier)
136            and self.normalization_strategy is NormalizationStrategy.CASE_INSENSITIVE
137        ):
138            parent = expression.parent
139            while isinstance(parent, exp.Dot):
140                parent = parent.parent
141
142            # In BigQuery, CTEs are case-insensitive, but UDF and table names are case-sensitive
143            # by default. The following check uses a heuristic to detect tables based on whether
144            # they are qualified. This should generally be correct, because tables in BigQuery
145            # must be qualified with at least a dataset, unless @@dataset_id is set.
146            case_sensitive = (
147                isinstance(parent, exp.UserDefinedFunction)
148                or (
149                    isinstance(parent, exp.Table)
150                    and parent.db
151                    and (parent.meta_get("quoted_table") or not parent.meta_get("maybe_column"))
152                )
153                or expression.meta_get("is_table")
154            )
155            if not case_sensitive:
156                expression.set("this", expression.this.lower())
157
158            return t.cast(E, expression)
159
160        return super().normalize_identifier(expression)
161
162    class JSONPathTokenizer(jsonpath.JSONPathTokenizer):
163        VAR_TOKENS = {
164            *jsonpath.JSONPathTokenizer.VAR_TOKENS,
165            TokenType.DASH,
166            TokenType.NUMBER,
167        }
168
169    class Tokenizer(tokens.Tokenizer):
170        QUOTES = ["'", '"', '"""', "'''"]
171        COMMENTS = ["--", "#", ("/*", "*/")]
172        IDENTIFIERS = ["`"]
173        STRING_ESCAPES = ["\\"]
174
175        HEX_STRINGS = [("0x", ""), ("0X", "")]
176
177        BYTE_STRINGS = [(prefix + q, q) for q in t.cast(list[str], QUOTES) for prefix in ("b", "B")]
178
179        RAW_STRINGS = [(prefix + q, q) for q in t.cast(list[str], QUOTES) for prefix in ("r", "R")]
180
181        NESTED_COMMENTS = False
182
183        KEYWORDS = {
184            **tokens.Tokenizer.KEYWORDS,
185            "ANY TYPE": TokenType.VARIANT,
186            "BEGIN": TokenType.COMMAND,
187            "BEGIN TRANSACTION": TokenType.BEGIN,
188            "BYTEINT": TokenType.INT,
189            "BYTES": TokenType.BINARY,
190            "CURRENT_DATETIME": TokenType.CURRENT_DATETIME,
191            "DATETIME": TokenType.TIMESTAMP,
192            "DECLARE": TokenType.DECLARE,
193            "ELSEIF": TokenType.COMMAND,
194            "EXCEPTION": TokenType.COMMAND,
195            "EXPORT": TokenType.EXPORT,
196            "FLOAT64": TokenType.DOUBLE,
197            "FOR SYSTEM TIME": TokenType.TIMESTAMP_SNAPSHOT,
198            "FOR SYSTEM_TIME": TokenType.TIMESTAMP_SNAPSHOT,
199            "LOOP": TokenType.COMMAND,
200            "MODEL": TokenType.MODEL,
201            "NOT DETERMINISTIC": TokenType.VOLATILE,
202            "RECORD": TokenType.STRUCT,
203            "REPEAT": TokenType.COMMAND,
204            "TIMESTAMP": TokenType.TIMESTAMPTZ,
205            "WHILE": TokenType.COMMAND,
206        }
207        KEYWORDS.pop("DIV")
208        KEYWORDS.pop("VALUES")
209        KEYWORDS.pop("/*+")
210
211    Parser = BigQueryParser
212
213    Generator = BigQueryGenerator

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

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