sqlglot.dialects.fabric

 31class Fabric(TSQL):
 32    """
 33    Microsoft Fabric Data Warehouse dialect that inherits from T-SQL.
 34
 35    Microsoft Fabric is a cloud-based analytics platform that provides a unified
 36    data warehouse experience. While it shares much of T-SQL's syntax, it has
 37    specific differences and limitations that this dialect addresses.
 38
 39    Key differences from T-SQL:
 40    - Case-sensitive identifiers (unlike T-SQL which is case-insensitive)
 41    - Limited data type support with mappings to supported alternatives
 42    - Temporal types (DATETIME2, DATETIMEOFFSET, TIME) limited to 6 digits precision
 43    - Certain legacy types (MONEY, SMALLMONEY, etc.) are not supported
 44    - Unicode types (NCHAR, NVARCHAR) are mapped to non-unicode equivalents
 45
 46    References:
 47    - Data Types: https://learn.microsoft.com/en-us/fabric/data-warehouse/data-types
 48    - T-SQL Surface Area: https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area
 49    """
 50
 51    # Fabric is case-sensitive unlike T-SQL which is case-insensitive
 52    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_SENSITIVE
 53
 54    class Tokenizer(TSQL.Tokenizer):
 55        # Override T-SQL tokenizer to handle TIMESTAMP differently
 56        # In T-SQL, TIMESTAMP is a synonym for ROWVERSION, but in Fabric we want it to be a datetime type
 57        # Also add UTINYINT keyword mapping since T-SQL doesn't have it
 58        KEYWORDS = {
 59            **TSQL.Tokenizer.KEYWORDS,
 60            "TIMESTAMP": TokenType.TIMESTAMP,
 61            "UTINYINT": TokenType.UTINYINT,
 62        }
 63
 64    class Generator(TSQL.Generator):
 65        # Fabric-specific type mappings - override T-SQL types that aren't supported
 66        # Reference: https://learn.microsoft.com/en-us/fabric/data-warehouse/data-types
 67        TYPE_MAPPING = {
 68            **TSQL.Generator.TYPE_MAPPING,
 69            exp.DataType.Type.DATETIME: "DATETIME2",
 70            exp.DataType.Type.DECIMAL: "DECIMAL",
 71            exp.DataType.Type.IMAGE: "VARBINARY",
 72            exp.DataType.Type.INT: "INT",
 73            exp.DataType.Type.JSON: "VARCHAR",
 74            exp.DataType.Type.MONEY: "DECIMAL",
 75            exp.DataType.Type.NCHAR: "CHAR",
 76            exp.DataType.Type.NVARCHAR: "VARCHAR",
 77            exp.DataType.Type.ROWVERSION: "ROWVERSION",
 78            exp.DataType.Type.SMALLDATETIME: "DATETIME2",
 79            exp.DataType.Type.SMALLMONEY: "DECIMAL",
 80            exp.DataType.Type.TIMESTAMP: "DATETIME2",
 81            exp.DataType.Type.TIMESTAMPNTZ: "DATETIME2",
 82            exp.DataType.Type.TIMESTAMPTZ: "DATETIME2",
 83            exp.DataType.Type.TINYINT: "SMALLINT",
 84            exp.DataType.Type.UTINYINT: "SMALLINT",
 85            exp.DataType.Type.UUID: "VARBINARY(MAX)",
 86            exp.DataType.Type.XML: "VARCHAR",
 87        }
 88
 89        def datatype_sql(self, expression: exp.DataType) -> str:
 90            # Check if this is a temporal type that needs precision handling. Fabric limits temporal
 91            # types to max 6 digits precision. When no precision is specified, we default to 6 digits.
 92            if (
 93                expression.is_type(*exp.DataType.TEMPORAL_TYPES)
 94                and expression.this != exp.DataType.Type.DATE
 95            ):
 96                # Create a new expression with the capped precision
 97                expression = _cap_data_type_precision(expression)
 98
 99            return super().datatype_sql(expression)
100
101        def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
102            # Cast to DATETIMEOFFSET if inside an AT TIME ZONE expression
103            # https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql#microsoft-fabric-support
104            if expression.is_type(exp.DataType.Type.TIMESTAMPTZ):
105                at_time_zone = expression.find_ancestor(exp.AtTimeZone, exp.Select)
106
107                # Return normal cast, if the expression is not in an AT TIME ZONE context
108                if not isinstance(at_time_zone, exp.AtTimeZone):
109                    return super().cast_sql(expression, safe_prefix)
110
111                # Get the precision from the original TIMESTAMPTZ cast and cap it to 6
112                capped_data_type = _cap_data_type_precision(expression.to, max_precision=6)
113                precision = capped_data_type.find(exp.DataTypeParam)
114                precision_value = (
115                    precision.this.to_py() if precision and precision.this.is_int else 6
116                )
117
118                # Do the cast explicitly to bypass sqlglot's default handling
119                datetimeoffset = f"CAST({expression.this} AS DATETIMEOFFSET({precision_value}))"
120
121                return self.sql(datetimeoffset)
122
123            return super().cast_sql(expression, safe_prefix)
124
125        def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
126            # Wrap the AT TIME ZONE expression in a cast to DATETIME2 if it contains a TIMESTAMPTZ
127            ## https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql#microsoft-fabric-support
128            timestamptz_cast = expression.find(exp.Cast)
129            if timestamptz_cast and timestamptz_cast.to.is_type(exp.DataType.Type.TIMESTAMPTZ):
130                # Get the precision from the original TIMESTAMPTZ cast and cap it to 6
131                data_type = timestamptz_cast.to
132                capped_data_type = _cap_data_type_precision(data_type, max_precision=6)
133                precision_param = capped_data_type.find(exp.DataTypeParam)
134                precision = precision_param.this.to_py() if precision_param else 6
135
136                # Generate the AT TIME ZONE expression (which will handle the inner cast conversion)
137                at_time_zone_sql = super().attimezone_sql(expression)
138
139                # Wrap it in an outer cast to DATETIME2
140                return f"CAST({at_time_zone_sql} AS DATETIME2({precision}))"
141
142            return super().attimezone_sql(expression)
143
144        def unixtotime_sql(self, expression: exp.UnixToTime) -> str:
145            scale = expression.args.get("scale")
146            timestamp = expression.this
147
148            if scale not in (None, exp.UnixToTime.SECONDS):
149                self.unsupported(f"UnixToTime scale {scale} is not supported by Fabric")
150                return ""
151
152            # Convert unix timestamp (seconds) to microseconds and round to avoid decimals
153            microseconds = timestamp * exp.Literal.number("1e6")
154            rounded = exp.func("round", microseconds, 0)
155            rounded_ms_as_bigint = exp.cast(rounded, exp.DataType.Type.BIGINT)
156
157            # Create the base datetime as '1970-01-01' cast to DATETIME2(6)
158            epoch_start = exp.cast("'1970-01-01'", "datetime2(6)", dialect="fabric")
159
160            dateadd = exp.DateAdd(
161                this=epoch_start,
162                expression=rounded_ms_as_bigint,
163                unit=exp.Literal.string("MICROSECONDS"),
164            )
165            return self.sql(dateadd)

54    class Tokenizer(TSQL.Tokenizer):
55        # Override T-SQL tokenizer to handle TIMESTAMP differently
56        # In T-SQL, TIMESTAMP is a synonym for ROWVERSION, but in Fabric we want it to be a datetime type
57        # Also add UTINYINT keyword mapping since T-SQL doesn't have it
58        KEYWORDS = {
59            **TSQL.Tokenizer.KEYWORDS,
60            "TIMESTAMP": TokenType.TIMESTAMP,
61            "UTINYINT": TokenType.UTINYINT,
62        }

 64    class Generator(TSQL.Generator):
 65        # Fabric-specific type mappings - override T-SQL types that aren't supported
 66        # Reference: https://learn.microsoft.com/en-us/fabric/data-warehouse/data-types
 67        TYPE_MAPPING = {
 68            **TSQL.Generator.TYPE_MAPPING,
 69            exp.DataType.Type.DATETIME: "DATETIME2",
 70            exp.DataType.Type.DECIMAL: "DECIMAL",
 71            exp.DataType.Type.IMAGE: "VARBINARY",
 72            exp.DataType.Type.INT: "INT",
 73            exp.DataType.Type.JSON: "VARCHAR",
 74            exp.DataType.Type.MONEY: "DECIMAL",
 75            exp.DataType.Type.NCHAR: "CHAR",
 76            exp.DataType.Type.NVARCHAR: "VARCHAR",
 77            exp.DataType.Type.ROWVERSION: "ROWVERSION",
 78            exp.DataType.Type.SMALLDATETIME: "DATETIME2",
 79            exp.DataType.Type.SMALLMONEY: "DECIMAL",
 80            exp.DataType.Type.TIMESTAMP: "DATETIME2",
 81            exp.DataType.Type.TIMESTAMPNTZ: "DATETIME2",
 82            exp.DataType.Type.TIMESTAMPTZ: "DATETIME2",
 83            exp.DataType.Type.TINYINT: "SMALLINT",
 84            exp.DataType.Type.UTINYINT: "SMALLINT",
 85            exp.DataType.Type.UUID: "VARBINARY(MAX)",
 86            exp.DataType.Type.XML: "VARCHAR",
 87        }
 88
 89        def datatype_sql(self, expression: exp.DataType) -> str:
 90            # Check if this is a temporal type that needs precision handling. Fabric limits temporal
 91            # types to max 6 digits precision. When no precision is specified, we default to 6 digits.
 92            if (
 93                expression.is_type(*exp.DataType.TEMPORAL_TYPES)
 94                and expression.this != exp.DataType.Type.DATE
 95            ):
 96                # Create a new expression with the capped precision
 97                expression = _cap_data_type_precision(expression)
 98
 99            return super().datatype_sql(expression)
100
101        def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
102            # Cast to DATETIMEOFFSET if inside an AT TIME ZONE expression
103            # https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql#microsoft-fabric-support
104            if expression.is_type(exp.DataType.Type.TIMESTAMPTZ):
105                at_time_zone = expression.find_ancestor(exp.AtTimeZone, exp.Select)
106
107                # Return normal cast, if the expression is not in an AT TIME ZONE context
108                if not isinstance(at_time_zone, exp.AtTimeZone):
109                    return super().cast_sql(expression, safe_prefix)
110
111                # Get the precision from the original TIMESTAMPTZ cast and cap it to 6
112                capped_data_type = _cap_data_type_precision(expression.to, max_precision=6)
113                precision = capped_data_type.find(exp.DataTypeParam)
114                precision_value = (
115                    precision.this.to_py() if precision and precision.this.is_int else 6
116                )
117
118                # Do the cast explicitly to bypass sqlglot's default handling
119                datetimeoffset = f"CAST({expression.this} AS DATETIMEOFFSET({precision_value}))"
120
121                return self.sql(datetimeoffset)
122
123            return super().cast_sql(expression, safe_prefix)
124
125        def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
126            # Wrap the AT TIME ZONE expression in a cast to DATETIME2 if it contains a TIMESTAMPTZ
127            ## https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql#microsoft-fabric-support
128            timestamptz_cast = expression.find(exp.Cast)
129            if timestamptz_cast and timestamptz_cast.to.is_type(exp.DataType.Type.TIMESTAMPTZ):
130                # Get the precision from the original TIMESTAMPTZ cast and cap it to 6
131                data_type = timestamptz_cast.to
132                capped_data_type = _cap_data_type_precision(data_type, max_precision=6)
133                precision_param = capped_data_type.find(exp.DataTypeParam)
134                precision = precision_param.this.to_py() if precision_param else 6
135
136                # Generate the AT TIME ZONE expression (which will handle the inner cast conversion)
137                at_time_zone_sql = super().attimezone_sql(expression)
138
139                # Wrap it in an outer cast to DATETIME2
140                return f"CAST({at_time_zone_sql} AS DATETIME2({precision}))"
141
142            return super().attimezone_sql(expression)
143
144        def unixtotime_sql(self, expression: exp.UnixToTime) -> str:
145            scale = expression.args.get("scale")
146            timestamp = expression.this
147
148            if scale not in (None, exp.UnixToTime.SECONDS):
149                self.unsupported(f"UnixToTime scale {scale} is not supported by Fabric")
150                return ""
151
152            # Convert unix timestamp (seconds) to microseconds and round to avoid decimals
153            microseconds = timestamp * exp.Literal.number("1e6")
154            rounded = exp.func("round", microseconds, 0)
155            rounded_ms_as_bigint = exp.cast(rounded, exp.DataType.Type.BIGINT)
156
157            # Create the base datetime as '1970-01-01' cast to DATETIME2(6)
158            epoch_start = exp.cast("'1970-01-01'", "datetime2(6)", dialect="fabric")
159
160            dateadd = exp.DateAdd(
161                this=epoch_start,
162                expression=rounded_ms_as_bigint,
163                unit=exp.Literal.string("MICROSECONDS"),
164            )
165            return self.sql(dateadd)