sqlglot.dialects.fabric

 12class Fabric(TSQL):
 13    """
 14    Microsoft Fabric Data Warehouse dialect that inherits from T-SQL.
 15
 16    Microsoft Fabric is a cloud-based analytics platform that provides a unified
 17    data warehouse experience. While it shares much of T-SQL's syntax, it has
 18    specific differences and limitations that this dialect addresses.
 19
 20    Key differences from T-SQL:
 21    - Case-sensitive identifiers (unlike T-SQL which is case-insensitive)
 22    - Limited data type support with mappings to supported alternatives
 23    - Temporal types (DATETIME2, DATETIMEOFFSET, TIME) limited to 6 digits precision
 24    - Certain legacy types (MONEY, SMALLMONEY, etc.) are not supported
 25    - Unicode types (NCHAR, NVARCHAR) are mapped to non-unicode equivalents
 26
 27    References:
 28    - Data Types: https://learn.microsoft.com/en-us/fabric/data-warehouse/data-types
 29    - T-SQL Surface Area: https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area
 30    """
 31
 32    # Fabric is case-sensitive unlike T-SQL which is case-insensitive
 33    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_SENSITIVE
 34
 35    class Tokenizer(TSQL.Tokenizer):
 36        # Override T-SQL tokenizer to handle TIMESTAMP differently
 37        # In T-SQL, TIMESTAMP is a synonym for ROWVERSION, but in Fabric we want it to be a datetime type
 38        # Also add UTINYINT keyword mapping since T-SQL doesn't have it
 39        KEYWORDS = {
 40            **TSQL.Tokenizer.KEYWORDS,
 41            "TIMESTAMP": TokenType.TIMESTAMP,
 42            "UTINYINT": TokenType.UTINYINT,
 43        }
 44
 45    class Generator(TSQL.Generator):
 46        # Fabric-specific type mappings - override T-SQL types that aren't supported
 47        # Reference: https://learn.microsoft.com/en-us/fabric/data-warehouse/data-types
 48        TYPE_MAPPING = {
 49            **TSQL.Generator.TYPE_MAPPING,
 50            exp.DataType.Type.DATETIME: "DATETIME2",
 51            exp.DataType.Type.DECIMAL: "DECIMAL",
 52            exp.DataType.Type.IMAGE: "VARBINARY",
 53            exp.DataType.Type.INT: "INT",
 54            exp.DataType.Type.JSON: "VARCHAR",
 55            exp.DataType.Type.MONEY: "DECIMAL",
 56            exp.DataType.Type.NCHAR: "CHAR",
 57            exp.DataType.Type.NVARCHAR: "VARCHAR",
 58            exp.DataType.Type.ROWVERSION: "ROWVERSION",
 59            exp.DataType.Type.SMALLDATETIME: "DATETIME2",
 60            exp.DataType.Type.SMALLMONEY: "DECIMAL",
 61            exp.DataType.Type.TIMESTAMP: "DATETIME2",
 62            exp.DataType.Type.TIMESTAMPNTZ: "DATETIME2",
 63            exp.DataType.Type.TIMESTAMPTZ: "DATETIMEOFFSET",
 64            exp.DataType.Type.TINYINT: "SMALLINT",
 65            exp.DataType.Type.UTINYINT: "SMALLINT",
 66            exp.DataType.Type.UUID: "VARBINARY(MAX)",
 67            exp.DataType.Type.XML: "VARCHAR",
 68        }
 69
 70        def datatype_sql(self, expression: exp.DataType) -> str:
 71            # Check if this is a temporal type that needs precision handling. Fabric limits temporal
 72            # types to max 6 digits precision. When no precision is specified, we default to 6 digits.
 73            if (
 74                expression.is_type(*exp.DataType.TEMPORAL_TYPES)
 75                and expression.this != exp.DataType.Type.DATE
 76            ):
 77                # Get the current precision (first expression if it exists)
 78                precision_param = expression.find(exp.DataTypeParam)
 79                target_precision = 6
 80
 81                if precision_param and precision_param.this.is_int:
 82                    # Cap precision at 6
 83                    current_precision = precision_param.this.to_py()
 84                    target_precision = min(current_precision, 6)
 85                else:
 86                    # If precision exists but is not an integer, default to 6
 87                    target_precision = 6
 88
 89                # Create a new expression with the target precision
 90                expression = exp.DataType(
 91                    this=expression.this,
 92                    expressions=[exp.DataTypeParam(this=exp.Literal.number(target_precision))],
 93                )
 94
 95            return super().datatype_sql(expression)
 96
 97        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
 98            # Special handling for TIMESTAMPTZ casts in Fabric
 99            if expression.to and expression.to.is_type(exp.DataType.Type.TIMESTAMPTZ):
100                attimezone = expression.find_ancestor(exp.AtTimeZone, exp.Select)
101                if not isinstance(attimezone, exp.AtTimeZone):
102                    # We're not inside an AT TIME ZONE, so wrap the cast in AT TIME ZONE 'UTC'
103                    at_time_zone = exp.AtTimeZone(this=expression, zone=exp.Literal.string("UTC"))
104                    return self.sql(at_time_zone)
105
106            return super().cast_sql(expression, safe_prefix)
107
108        def unixtotime_sql(self, expression: exp.UnixToTime) -> str:
109            scale = expression.args.get("scale")
110            timestamp = expression.this
111
112            if scale not in (None, exp.UnixToTime.SECONDS):
113                self.unsupported(f"UnixToTime scale {scale} is not supported by Fabric")
114                return ""
115
116            # Convert unix timestamp (seconds) to microseconds and round to avoid decimals
117            microseconds = timestamp * exp.Literal.number("1e6")
118            rounded = exp.func("round", microseconds, 0)
119            rounded_ms_as_bigint = exp.cast(rounded, exp.DataType.Type.BIGINT)
120
121            # Create the base datetime as '1970-01-01' cast to DATETIME2(6)
122            epoch_start = exp.cast("'1970-01-01'", "datetime2(6)", dialect="fabric")
123
124            dateadd = exp.DateAdd(
125                this=epoch_start,
126                expression=rounded_ms_as_bigint,
127                unit=exp.Literal.string("MICROSECONDS"),
128            )
129            return self.sql(dateadd)

35    class Tokenizer(TSQL.Tokenizer):
36        # Override T-SQL tokenizer to handle TIMESTAMP differently
37        # In T-SQL, TIMESTAMP is a synonym for ROWVERSION, but in Fabric we want it to be a datetime type
38        # Also add UTINYINT keyword mapping since T-SQL doesn't have it
39        KEYWORDS = {
40            **TSQL.Tokenizer.KEYWORDS,
41            "TIMESTAMP": TokenType.TIMESTAMP,
42            "UTINYINT": TokenType.UTINYINT,
43        }

 45    class Generator(TSQL.Generator):
 46        # Fabric-specific type mappings - override T-SQL types that aren't supported
 47        # Reference: https://learn.microsoft.com/en-us/fabric/data-warehouse/data-types
 48        TYPE_MAPPING = {
 49            **TSQL.Generator.TYPE_MAPPING,
 50            exp.DataType.Type.DATETIME: "DATETIME2",
 51            exp.DataType.Type.DECIMAL: "DECIMAL",
 52            exp.DataType.Type.IMAGE: "VARBINARY",
 53            exp.DataType.Type.INT: "INT",
 54            exp.DataType.Type.JSON: "VARCHAR",
 55            exp.DataType.Type.MONEY: "DECIMAL",
 56            exp.DataType.Type.NCHAR: "CHAR",
 57            exp.DataType.Type.NVARCHAR: "VARCHAR",
 58            exp.DataType.Type.ROWVERSION: "ROWVERSION",
 59            exp.DataType.Type.SMALLDATETIME: "DATETIME2",
 60            exp.DataType.Type.SMALLMONEY: "DECIMAL",
 61            exp.DataType.Type.TIMESTAMP: "DATETIME2",
 62            exp.DataType.Type.TIMESTAMPNTZ: "DATETIME2",
 63            exp.DataType.Type.TIMESTAMPTZ: "DATETIMEOFFSET",
 64            exp.DataType.Type.TINYINT: "SMALLINT",
 65            exp.DataType.Type.UTINYINT: "SMALLINT",
 66            exp.DataType.Type.UUID: "VARBINARY(MAX)",
 67            exp.DataType.Type.XML: "VARCHAR",
 68        }
 69
 70        def datatype_sql(self, expression: exp.DataType) -> str:
 71            # Check if this is a temporal type that needs precision handling. Fabric limits temporal
 72            # types to max 6 digits precision. When no precision is specified, we default to 6 digits.
 73            if (
 74                expression.is_type(*exp.DataType.TEMPORAL_TYPES)
 75                and expression.this != exp.DataType.Type.DATE
 76            ):
 77                # Get the current precision (first expression if it exists)
 78                precision_param = expression.find(exp.DataTypeParam)
 79                target_precision = 6
 80
 81                if precision_param and precision_param.this.is_int:
 82                    # Cap precision at 6
 83                    current_precision = precision_param.this.to_py()
 84                    target_precision = min(current_precision, 6)
 85                else:
 86                    # If precision exists but is not an integer, default to 6
 87                    target_precision = 6
 88
 89                # Create a new expression with the target precision
 90                expression = exp.DataType(
 91                    this=expression.this,
 92                    expressions=[exp.DataTypeParam(this=exp.Literal.number(target_precision))],
 93                )
 94
 95            return super().datatype_sql(expression)
 96
 97        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
 98            # Special handling for TIMESTAMPTZ casts in Fabric
 99            if expression.to and expression.to.is_type(exp.DataType.Type.TIMESTAMPTZ):
100                attimezone = expression.find_ancestor(exp.AtTimeZone, exp.Select)
101                if not isinstance(attimezone, exp.AtTimeZone):
102                    # We're not inside an AT TIME ZONE, so wrap the cast in AT TIME ZONE 'UTC'
103                    at_time_zone = exp.AtTimeZone(this=expression, zone=exp.Literal.string("UTC"))
104                    return self.sql(at_time_zone)
105
106            return super().cast_sql(expression, safe_prefix)
107
108        def unixtotime_sql(self, expression: exp.UnixToTime) -> str:
109            scale = expression.args.get("scale")
110            timestamp = expression.this
111
112            if scale not in (None, exp.UnixToTime.SECONDS):
113                self.unsupported(f"UnixToTime scale {scale} is not supported by Fabric")
114                return ""
115
116            # Convert unix timestamp (seconds) to microseconds and round to avoid decimals
117            microseconds = timestamp * exp.Literal.number("1e6")
118            rounded = exp.func("round", microseconds, 0)
119            rounded_ms_as_bigint = exp.cast(rounded, exp.DataType.Type.BIGINT)
120
121            # Create the base datetime as '1970-01-01' cast to DATETIME2(6)
122            epoch_start = exp.cast("'1970-01-01'", "datetime2(6)", dialect="fabric")
123
124            dateadd = exp.DateAdd(
125                this=epoch_start,
126                expression=rounded_ms_as_bigint,
127                unit=exp.Literal.string("MICROSECONDS"),
128            )
129            return self.sql(dateadd)