0
# SQL Integration
1

2
Bidirectional SQL integration allowing parsing SQL into expressions and compiling expressions to SQL with backend-specific optimizations.
3

4
## Capabilities
5

6
### SQL to Expressions
7

8
Parse SQL queries into Ibis expressions.
9

10
```python { .api }
11
def parse_sql(sql, dialect=None):
12
    """
13
    Parse SQL string into Ibis expression.
14
    
15
    Parameters:
16
    - sql: str, SQL query string
17
    - dialect: str, optional SQL dialect ('postgres', 'mysql', 'bigquery', etc.)
18
    
19
    Returns:
20
    Table expression representing the SQL query
21
    """
22
```
23

24
**Usage Examples:**
25
```python
26
import ibis
27

28
# Parse simple SQL
29
sql = "SELECT name, age FROM employees WHERE age > 25"
30
expr = ibis.parse_sql(sql)
31

32
# Parse with dialect
33
bigquery_sql = """
34
SELECT 
35
    customer_id,
36
    EXTRACT(YEAR FROM order_date) as year,
37
    SUM(amount) as total_amount
38
FROM `project.dataset.orders`
39
WHERE order_date >= '2023-01-01'
40
GROUP BY customer_id, year
41
ORDER BY total_amount DESC
42
"""
43
expr = ibis.parse_sql(bigquery_sql, dialect='bigquery')
44

45
# Complex SQL with window functions
46
window_sql = """
47
SELECT 
48
    employee_id,
49
    salary,
50
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
51
    LAG(salary, 1) OVER (ORDER BY hire_date) as prev_salary
52
FROM employees
53
"""
54
expr = ibis.parse_sql(window_sql)
55
```
56

57
### Expressions to SQL
58

59
Compile Ibis expressions to SQL for specific backends.
60

61
```python { .api }
62
def to_sql(expr, dialect=None):
63
    """
64
    Compile expression to SQL string.
65
    
66
    Parameters:
67
    - expr: Ibis expression to compile
68
    - dialect: str, target SQL dialect
69
    
70
    Returns:
71
    SQL query string
72
    """
73
```
74

75
**Usage Examples:**
76
```python
77
# Create expression
78
table = ibis.table({'name': 'string', 'age': 'int64', 'salary': 'float64'})
79
expr = (
80
    table
81
    .filter(table.age > 25)
82
    .group_by('name')
83
    .aggregate(avg_salary=table.salary.mean())
84
    .order_by('avg_salary')
85
)
86

87
# Compile to different dialects
88
postgres_sql = ibis.to_sql(expr, dialect='postgres')
89
bigquery_sql = ibis.to_sql(expr, dialect='bigquery')
90
mysql_sql = ibis.to_sql(expr, dialect='mysql')
91

92
print(postgres_sql)
93
# SELECT "name", AVG("salary") AS "avg_salary"
94
# FROM "table" 
95
# WHERE "age" > 25
96
# GROUP BY "name"
97
# ORDER BY "avg_salary"
98
```
99

100
### Backend SQL Compilation
101

102
Use backend connections to compile expressions to their native SQL dialect.
103

104
```python { .api }
105
backend.compile(expr):
106
    """
107
    Compile expression to backend-specific SQL.
108
    
109
    Parameters:
110
    - expr: Ibis expression
111
    
112
    Returns:
113
    SQL string optimized for the backend
114
    """
115
```
116

117
**Usage Examples:**
118
```python
119
# Backend-specific compilation
120
duckdb_con = ibis.duckdb.connect()
121
pg_con = ibis.postgres.connect(...)
122

123
# Same expression, different SQL output
124
expr = table.filter(table.value > 100).select('*')
125

126
duckdb_sql = duckdb_con.compile(expr)
127
postgres_sql = pg_con.compile(expr)
128

129
# DuckDB might use different syntax or optimizations than PostgreSQL
130
print("DuckDB:", duckdb_sql)
131
print("PostgreSQL:", postgres_sql)
132
```
133

134
### Raw SQL Execution
135

136
Execute raw SQL directly on backend connections.
137

138
```python { .api }
139
backend.raw_sql(query):
140
    """
141
    Execute raw SQL query.
142
    
143
    Parameters:
144
    - query: str, SQL query to execute
145
    
146
    Returns:
147
    Query results (backend-specific format)
148
    """
149
```
150

151
**Usage Examples:**
152
```python
153
# Execute raw SQL
154
con = ibis.postgres.connect(...)
155

156
# Simple query
157
result = con.raw_sql("SELECT version()")
158

159
# Complex administrative query
160
result = con.raw_sql("""
161
    SELECT 
162
        schemaname,
163
        tablename,
164
        pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
165
    FROM pg_tables 
166
    WHERE schemaname = 'public'
167
    ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
168
""")
169
```
170

171
### SQL and Expression Decompilation
172

173
Convert expressions back to Python code.
174

175
```python { .api }
176
def decompile(expr):
177
    """
178
    Decompile expression to Python code string.
179
    
180
    Parameters:
181
    - expr: Ibis expression to decompile
182
    
183
    Returns:
184
    Python code string that recreates the expression
185
    """
186
```
187

188
**Usage Examples:**
189
```python
190
# Create complex expression
191
table = ibis.table({'x': 'int64', 'y': 'string', 'z': 'float64'})
192
expr = (
193
    table
194
    .filter(table.x > 10)
195
    .mutate(
196
        x_squared=table.x ** 2,
197
        y_upper=table.y.upper()
198
    )
199
    .group_by('y_upper')
200
    .aggregate(
201
        count=table.count(),
202
        avg_x=table.x.mean(),
203
        sum_z=table.z.sum()
204
    )
205
)
206

207
# Decompile to Python code
208
python_code = ibis.decompile(expr)
209
print(python_code)
210
# Output: Python code that recreates the expression
211
```
212

213
### SQL Template Integration
214

215
Use Ibis expressions within SQL templates.
216

217
**Usage Examples:**
218
```python
219
# SQL with Ibis expression components
220
base_filter = table.date_col >= '2023-01-01'
221
aggregation = table.sales.sum()
222

223
# Combine in SQL template
224
sql_template = f"""
225
WITH filtered_data AS (
226
    {ibis.to_sql(table.filter(base_filter))}
227
),
228
aggregated AS (
229
    SELECT 
230
        category,
231
        {ibis.to_sql(aggregation)} as total_sales
232
    FROM filtered_data
233
    GROUP BY category
234
)
235
SELECT * FROM aggregated
236
ORDER BY total_sales DESC
237
"""
238

239
result = con.raw_sql(sql_template)
240
```
241

242
### Cross-Backend SQL Translation
243

244
Translate SQL between different backend dialects.
245

246
**Usage Examples:**
247
```python
248
# Parse SQL from one dialect
249
mysql_sql = """
250
SELECT 
251
    DATE_FORMAT(created_at, '%Y-%m') as month,
252
    COUNT(*) as orders
253
FROM orders 
254
WHERE created_at >= '2023-01-01'
255
GROUP BY month
256
ORDER BY month
257
"""
258

259
# Parse and translate
260
expr = ibis.parse_sql(mysql_sql, dialect='mysql')
261

262
# Compile for different backends
263
postgres_sql = ibis.to_sql(expr, dialect='postgres')
264
bigquery_sql = ibis.to_sql(expr, dialect='bigquery')
265

266
print("PostgreSQL:", postgres_sql)
267
print("BigQuery:", bigquery_sql)
268
```
269

270
### SQL Function Integration
271

272
Use backend-specific SQL functions in expressions.
273

274
**Usage Examples:**
275
```python
276
# Backend-specific functions
277
pg_con = ibis.postgres.connect(...)
278

279
# PostgreSQL-specific functions via raw SQL
280
pg_expr = pg_con.sql("""
281
    SELECT generate_series(1, 10) as numbers
282
""")
283

284
# Combine with Ibis operations
285
result = (
286
    pg_expr
287
    .mutate(
288
        squared=pg_expr.numbers ** 2,
289
        is_even=(pg_expr.numbers % 2) == 0
290
    )
291
    .filter(pg_expr.numbers > 5)
292
)
293

294
# Compile back to SQL
295
final_sql = pg_con.compile(result)
296
```
297

298
### SQL Optimization Hints
299

300
Add backend-specific optimization hints.
301

302
**Usage Examples:**
303
```python
304
# Expression with optimization context
305
expr = (
306
    table
307
    .filter(table.date_col >= '2023-01-01')
308
    .group_by('category')
309
    .aggregate(total=table.amount.sum())
310
)
311

312
# Backend-specific optimizations
313
bigquery_con = ibis.bigquery.connect(...)
314

315
# BigQuery-specific SQL with hints
316
optimized_sql = bigquery_con.compile(expr)
317
# Backend may add optimizations like partitioning hints
318
```
319

320
### SQL Debugging and Analysis
321

322
Tools for understanding generated SQL.
323

324
**Usage Examples:**
325
```python
326
# Debug expression compilation
327
expr = table.join(other_table, table.id == other_table.ref_id)
328

329
# Get SQL with formatting
330
sql = ibis.to_sql(expr, dialect='postgres')
331
print("Generated SQL:")
332
print(sql)
333

334
# Analyze query plan (backend-specific)
335
con = ibis.postgres.connect(...)
336
plan = con.raw_sql(f"EXPLAIN ANALYZE {sql}")
337
print("Query Plan:")
338
print(plan)
339

340
# Get expression tree
341
print("Expression Structure:")
342
print(repr(expr))
343
```
344

345
### SQL Compatibility Layers
346

347
Handle SQL dialect differences transparently.
348

349
**Usage Examples:**
350
```python
351
# Write dialect-agnostic expressions
352
expr = (
353
    table
354
    .mutate(
355
        # String concatenation (varies by backend)
356
        full_name=table.first_name + ' ' + table.last_name,
357
        # Date extraction (varies by backend) 
358
        year=table.date_col.year(),
359
        # Conditional logic (standardized)
360
        status=ibis.case()
361
            .when(table.age >= 18, 'adult')
362
            .else_('minor')
363
    )
364
)
365

366
# Automatically generates correct SQL for each backend
367
postgres_sql = ibis.to_sql(expr, dialect='postgres')
368
mysql_sql = ibis.to_sql(expr, dialect='mysql')
369
sqlite_sql = ibis.to_sql(expr, dialect='sqlite')
370

371
# Each uses backend-appropriate syntax for string concat, date functions, etc.
372
```