0
# Data Types
1
2
Comprehensive type system supporting both standard SQL types and Snowflake's semi-structured data types with full SQLAlchemy integration.
3
4
## Capabilities
5
6
### Standard SQL Types
7
8
Re-exported SQLAlchemy types for compatibility with standard SQL databases.
9
10
```python { .api }
11
from snowflake.sqlalchemy import (
12
BIGINT, BINARY, BOOLEAN, CHAR, DATE, DATETIME, DECIMAL,
13
FLOAT, INT, INTEGER, REAL, SMALLINT, TIME, TIMESTAMP, VARCHAR
14
)
15
16
# Type class definitions
17
class BIGINT(sqltypes.BIGINT): ...
18
class BINARY(sqltypes.BINARY): ...
19
class BOOLEAN(sqltypes.BOOLEAN): ...
20
class CHAR(sqltypes.CHAR): ...
21
class DATE(sqltypes.DATE): ...
22
class DATETIME(sqltypes.DATETIME): ...
23
class DECIMAL(sqltypes.DECIMAL): ...
24
class FLOAT(sqltypes.FLOAT): ...
25
class INT(sqltypes.INT): ...
26
class INTEGER(sqltypes.INTEGER): ...
27
class REAL(sqltypes.REAL): ...
28
class SMALLINT(sqltypes.SMALLINT): ...
29
class TIME(sqltypes.TIME): ...
30
class TIMESTAMP(sqltypes.TIMESTAMP): ...
31
class VARCHAR(sqltypes.VARCHAR): ...
32
```
33
34
### Snowflake Type Aliases
35
36
Convenient aliases for common Snowflake type names.
37
38
```python { .api }
39
from snowflake.sqlalchemy import (
40
TEXT, CHARACTER, DEC, DOUBLE, FIXED, NUMBER,
41
BYTEINT, STRING, TINYINT, VARBINARY
42
)
43
44
# Type aliases
45
TEXT = VARCHAR
46
CHARACTER = CHAR
47
DEC = DECIMAL
48
DOUBLE = FLOAT
49
FIXED = DECIMAL
50
NUMBER = DECIMAL
51
BYTEINT = SMALLINT
52
STRING = VARCHAR
53
TINYINT = SMALLINT
54
VARBINARY = BINARY
55
```
56
57
### Semi-Structured Types
58
59
Snowflake's native semi-structured data types for handling JSON, arrays, objects, and variant data.
60
61
```python { .api }
62
from snowflake.sqlalchemy import VARIANT, ARRAY, OBJECT, MAP
63
64
class VARIANT(SnowflakeType):
65
"""Semi-structured VARIANT type for JSON-like data."""
66
__visit_name__ = "VARIANT"
67
68
class ARRAY(StructuredType):
69
"""Typed array for homogeneous collections."""
70
__visit_name__ = "SNOWFLAKE_ARRAY"
71
72
def __init__(self, value_type=None, not_null=False):
73
"""
74
Create typed array.
75
76
Args:
77
value_type: Type of array elements
78
not_null: Whether array elements can be null
79
"""
80
81
class OBJECT(StructuredType):
82
"""Structured object type with named fields."""
83
__visit_name__ = "OBJECT"
84
85
def __init__(self, **items_types):
86
"""
87
Create structured object with typed fields.
88
89
Args:
90
**items_types: Field names and their types
91
"""
92
93
class MAP(StructuredType):
94
"""Typed map with key-value pairs."""
95
__visit_name__ = "MAP"
96
97
def __init__(self, key_type, value_type, not_null=False):
98
"""
99
Create typed map.
100
101
Args:
102
key_type: Type of map keys
103
value_type: Type of map values
104
not_null: Whether values can be null
105
"""
106
```
107
108
### Timestamp Types
109
110
Snowflake's specialized timestamp types with timezone handling.
111
112
```python { .api }
113
from snowflake.sqlalchemy import (
114
TIMESTAMP_TZ, TIMESTAMP_LTZ, TIMESTAMP_NTZ
115
)
116
117
class TIMESTAMP_TZ(SnowflakeType):
118
"""Timestamp with timezone."""
119
__visit_name__ = "TIMESTAMP_TZ"
120
121
class TIMESTAMP_LTZ(SnowflakeType):
122
"""Timestamp with local timezone."""
123
__visit_name__ = "TIMESTAMP_LTZ"
124
125
class TIMESTAMP_NTZ(SnowflakeType):
126
"""Timestamp without timezone."""
127
__visit_name__ = "TIMESTAMP_NTZ"
128
```
129
130
### Geospatial Types
131
132
Snowflake's geospatial data types for geographic and geometric data.
133
134
```python { .api }
135
from snowflake.sqlalchemy import GEOGRAPHY, GEOMETRY
136
137
class GEOGRAPHY(SnowflakeType):
138
"""Geographic data type for Earth-based coordinates."""
139
__visit_name__ = "GEOGRAPHY"
140
141
class GEOMETRY(SnowflakeType):
142
"""Geometric data type for arbitrary coordinate systems."""
143
__visit_name__ = "GEOMETRY"
144
```
145
146
### Base Classes
147
148
Foundation classes for Snowflake type system.
149
150
```python { .api }
151
class SnowflakeType(sqltypes.TypeEngine):
152
"""Base class for all Snowflake-specific types."""
153
154
def _default_dialect(self):
155
"""Get the default Snowflake dialect."""
156
157
class StructuredType(SnowflakeType):
158
"""Base class for semi-structured types."""
159
160
def __init__(self, is_semi_structured: bool = False):
161
"""
162
Initialize structured type.
163
164
Args:
165
is_semi_structured: Whether type is semi-structured
166
"""
167
```
168
169
## Usage Examples
170
171
### Basic Types
172
173
```python
174
from sqlalchemy import Column, MetaData, Table
175
from snowflake.sqlalchemy import VARIANT, ARRAY, TEXT, INTEGER
176
177
metadata = MetaData()
178
products = Table(
179
'products',
180
metadata,
181
Column('id', INTEGER, primary_key=True),
182
Column('name', TEXT()),
183
Column('metadata', VARIANT),
184
Column('tags', ARRAY(TEXT()))
185
)
186
```
187
188
### Structured Types
189
190
```python
191
from snowflake.sqlalchemy import OBJECT, MAP, ARRAY
192
193
# Object with typed fields
194
address_type = OBJECT(
195
street=TEXT(),
196
city=TEXT(),
197
zipcode=INTEGER
198
)
199
200
# Map with typed key-value pairs
201
attributes_type = MAP(TEXT(), VARIANT())
202
203
# Array of objects
204
addresses_type = ARRAY(address_type)
205
```
206
207
### Timestamp Types
208
209
```python
210
from snowflake.sqlalchemy import TIMESTAMP_TZ, TIMESTAMP_LTZ, TIMESTAMP_NTZ
211
212
events = Table(
213
'events',
214
metadata,
215
Column('id', INTEGER, primary_key=True),
216
Column('created_at_utc', TIMESTAMP_NTZ),
217
Column('created_at_user_tz', TIMESTAMP_TZ),
218
Column('created_at_local', TIMESTAMP_LTZ)
219
)
220
```
221
222
### Geospatial Types
223
224
```python
225
from snowflake.sqlalchemy import GEOGRAPHY, GEOMETRY
226
227
locations = Table(
228
'locations',
229
metadata,
230
Column('id', INTEGER, primary_key=True),
231
Column('coordinates', GEOGRAPHY),
232
Column('boundary', GEOMETRY)
233
)
234
```