Comments (6)
I think we're doing something wrong:
[ins] In [25]: exp = sg.parse_one("CREATE TEMPORARY TABLE temptest (name VARCHAR);",
...: dialect="duckdb")
[ins] In [26]: exp
Out[26]:
Create(
this=Schema(
this=Table(
this=Identifier(this=temptest, quoted=False)),
expressions=[
ColumnDef(
this=Identifier(this=name, quoted=False),
kind=DataType(this=Type.VARCHAR, nested=False))]),
kind=TABLE,
properties=Properties(
expressions=[
TemporaryProperty()]))
[ins] In [27]: exp.sql(dialect="mssql")
Out[27]: 'CREATE TABLE #temptest (name VARCHAR)'
from ibis.
FYI the temp table fix has been deployed with 23.13.0.
from ibis.
Hmm, yeah, something funky is definitely happening here. The create table SQL we're generating (this is for the starwars example): is
'CREATE TABLE #[ibis_mssql_table_sf22odm3xvcjxhkiuk5pk6o4py] ([name] VARCHAR(max), [height] BIGINT, [mass] FLOAT, [hair_color] VARCHAR(max), [skin_color] VARCHAR(max), [eye_color] VARCHAR(max), [birth_year] FLOAT, [sex] VARCHAR(max), [gender] VARCHAR(max), [homeworld] VARCHAR(max), [species] VARCHAR(max), [films] VARCHAR(max), [vehicles] VARCHAR(max), [starships] VARCHAR(max))'
Definitely don't need or want the #[]
around the table name
from ibis.
hmm, wait, I guess that's the mssql syntax for temporary tables? digging in a bit more
from ibis.
I think this is a sqlglot bug, although we may be constructing the expression incorrectly.
[nav] In [14]: query = """CREATE TABLE #crunch_no_brackets ([name] VARCHAR(max))"""
[ins] In [15]: with con.raw_sql(query) as c:
...: pass
[ins] In [16]: query = """CREATE TABLE [brackets_no_crunch] ([name] VARCHAR(max))"""
[ins] In [17]: with con.raw_sql(query) as c:
...: pass
[ins] In [18]: query = """CREATE TABLE #[brackets_with_crunch] ([name] VARCHAR(max))"""
[ins] In [19]: with con.raw_sql(query) as c:
...: pass
---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
Cell In[19], line 1
----> 1 with con.raw_sql(query) as c:
2 pass
File ~/github.com/ibis-project/ibis/ibis/backends/mssql/__init__.py:279, in Backend.raw_sql(self, query, **kwargs)
276 cursor = con.cursor()
278 try:
--> 279 cursor.execute(query, **kwargs)
280 except Exception:
281 con.rollback()
ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near 'brackets_with_crunch'. (102) (SQLExecDirectW)")
from ibis.
Ok, I think that there are some edge-cases that we are definitely not handling correctly.
That said, there is also a sqlglot bug:
[ins] In [1]: import sqlglot as sg
[ins] In [2]: exp = sg.parse_one(
...: "CREATE TEMPORARY TABLE 'temptest' (name VARCHAR);", dialect="duckd
...: b"
...: )
[ins] In [3]: exp
Out[3]:
Create(
this=Schema(
this=Table(
this=Identifier(this=temptest, quoted=True)),
expressions=[
ColumnDef(
this=Identifier(this=name, quoted=False),
kind=DataType(this=Type.VARCHAR, nested=False))]),
kind=TABLE,
properties=Properties(
expressions=[
TemporaryProperty()]))
[ins] In [4]: exp.sql(dialect="tsql")
Out[4]: 'CREATE TABLE #[temptest] (name VARCHAR)'
That last should be CREATE TABLE [#temptest] (name VARCHAR)
from ibis.
Related Issues (20)
- feat: add a table_exists(table_name) api HOT 1
- bug: `to_sql` always shows DuckDB SQL for a memtable even if there's a default backend set HOT 1
- Polars backend can read only 1 csv HOT 1
- docs: add ops docstrings
- ci: more granular cloud run labels
- feat: allow to connect to a duckdb named in-memory database
- feat(memtable): handle castable inputs from date-like strings to date HOT 3
- feat: better distinguish ibis scalars from evaluated results in interactive repr
- feat: add databricks support HOT 2
- bug: .insert() uses column position, not names, to align HOT 1
- feat(settings): generalize and extend settings passthrough dictionary
- feat: Leverage SQLGlot AST for query parsing HOT 2
- bug: EOFError when running ibis tutorial code with ClickHouse backend HOT 4
- bug: The function `parse_sql` fails when `COUNT(*)` is in the query HOT 4
- feat: Add the ability to pass regex flags HOT 2
- Config Value spark.sql.mapKeyDedupPolicy not Supported by Databricks SQL Warehouse HOT 1
- feat: profiling? HOT 2
- bug: ibis mssql backend can not create table for very simple pandas dataframe HOT 1
- bug: cannot convert mysql `datetime(6)` type to ibis datatype HOT 1
- bug: Failed to load Postgres table containing ENUM
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from ibis.