Repository: ibis-project/ibis

Issue:
Possibility to extract isoyear and isoweekday (or weekday)  from a timestamp column.
### Is your feature request related to a problem?

_No response_

### What is the motivation behind your request?

I'm working on a POC to migrate from `pandas` to `ibis`. Since `pandas` (and also `duckdb`) support this, it would be great if this is possible with `ibis` as well.

### Describe the solution you'd like

Add new methods that expose the functionality.

### What version of ibis are you running?

8.0.0

### What backend(s) are you using, if any?

DuckDB

### Code of Conduct

- [X] I agree to follow this project's Code of Conduct

Discussion:
github-actions[bot]: **ACTION NEEDED**

Ibis follows the [Conventional Commits specification](https://www.conventionalcommits.org/en/v1.0.0/) for release automation.

The PR title and description are used as the merge commit message.

Please update your PR title and description to match the specification.
kaijennissen: @cpcloud I'm not sure where the corresponding tests should be placed? 
- Inside `ibis/tests/expr/test_timestampy.py`
  - as new parametrization of `test_extract_fields`
  - as a new test function, f.e. `test_iso_year`
- Inside `ibis/backends/tests/test_temportal.py`

gforsyth: Hey @kaijennissen!

For tests of ibis internals, like does the extract operation return the Ibis type we expect for a given input, those go in `ibis/tests/expr`.

For testing execution against backends (does the operation do what we want, which backends are implemented, etc), those go in `ibis/backends/tests`

Patch:
diff --git a/ibis/backends/bigquery/compiler.py b/ibis/backends/bigquery/compiler.py
index b32a6476c..633d12708 100644
--- a/ibis/backends/bigquery/compiler.py
+++ b/ibis/backends/bigquery/compiler.py
@@ -391,6 +391,9 @@ class BigQueryCompiler(SQLGlotCompiler):
     def visit_ExtractWeekOfYear(self, op, *, arg):
         return self.f.extract(self.v.isoweek, arg)
 
+    def visit_ExtractIsoYear(self, op, *, arg):
+        return self.f.extract(self.v.isoyear, arg)
+
     def visit_ExtractMillisecond(self, op, *, arg):
         return self.f.extract(self.v.millisecond, arg)
 
diff --git a/ibis/backends/clickhouse/compiler.py b/ibis/backends/clickhouse/compiler.py
index c99635b5f..9b01beef6 100644
--- a/ibis/backends/clickhouse/compiler.py
+++ b/ibis/backends/clickhouse/compiler.py
@@ -72,6 +72,7 @@ class ClickHouseCompiler(SQLGlotCompiler):
         ops.ExtractSecond: "toSecond",
         ops.ExtractWeekOfYear: "toISOWeek",
         ops.ExtractYear: "toYear",
+        ops.ExtractIsoYear: "toISOYear",
         ops.First: "any",
         ops.IntegerRange: "range",
         ops.IsInf: "isInfinite",
diff --git a/ibis/backends/duckdb/__init__.py b/ibis/backends/duckdb/__init__.py
index 75d044067..04542d690 100644
--- a/ibis/backends/duckdb/__init__.py
+++ b/ibis/backends/duckdb/__init__.py
@@ -27,7 +27,7 @@ from ibis.backends import CanCreateDatabase, CanCreateSchema, UrlFromPath
 from ibis.backends.duckdb.compiler import DuckDBCompiler
 from ibis.backends.duckdb.converter import DuckDBPandasData
 from ibis.backends.sql import SQLBackend
-from ibis.backends.sql.compiler import STAR, C, F
+from ibis.backends.sql.compiler import STAR, C
 from ibis.expr.operations.udf import InputType
 
 if TYPE_CHECKING:
@@ -57,21 +57,17 @@ class _Settings:
 
     def __getitem__(self, key: str) -> Any:
         maybe_value = self.con.execute(
-            f"select value from duckdb_settings() where name = '{key}'"
+            "select value from duckdb_settings() where name = $1", [key]
         ).fetchone()
         if maybe_value is not None:
             return maybe_value[0]
         raise KeyError(key)
 
     def __setitem__(self, key, value):
-        self.con.execute(f"SET {key} = '{value}'")
+        self.con.execute(f"SET {key} = {str(value)!r}")
 
     def __repr__(self):
-        ((kv,),) = self.con.execute(
-            "select map(array_agg(name), array_agg(value)) from duckdb_settings()"
-        ).fetch()
-
-        return repr(dict(zip(kv["key"], kv["value"])))
+        return repr(self.con.sql("from duckdb_settings()"))
 
 
 class Backend(SQLBackend, CanCreateDatabase, CanCreateSchema, UrlFromPath):
@@ -154,6 +150,10 @@ class Backend(SQLBackend, CanCreateDatabase, CanCreateSchema, UrlFromPath):
         database
             The name of the database in which to create the table; if not
             passed, the current database is used.
+
+            For multi-level table hierarchies, you can pass in a dotted string
+            path like `"catalog.database"` or a tuple of strings like
+            `("catalog", "database")`.
         temp
             Create a temporary table
         overwrite
@@ -161,6 +161,20 @@ class Backend(SQLBackend, CanCreateDatabase, CanCreateSchema, UrlFromPath):
             if the table exists
 
         """
+        table_loc = self._to_sqlglot_table(database)
+
+        if getattr(table_loc, "catalog", False) and temp:
+            raise exc.UnsupportedArgumentError(
+                "DuckDB can only create temporary tables in the `temp` catalog. "
+                "Don't specify a catalog to enable temp table creation."
+            )
+
+        catalog = self.current_catalog
+        database = self.current_database
+        if table_loc is not None:
+            catalog = table_loc.catalog or catalog
+            database = table_loc.db or database
+
         if obj is None and schema is None:
             raise ValueError("Either `obj` or `schema` must be specified")
 
@@ -168,6 +182,7 @@ class Backend(SQLBackend, CanCreateDatabase, CanCreateSchema, UrlFromPath):
 
         if temp:
             properties.append(sge.TemporaryProperty())
+            catalog = "temp"
 
         temp_memtable_view = None
 
@@ -202,8 +217,10 @@ class Backend(SQLBackend, CanCreateDatabase, CanCreateSchema, UrlFromPath):
         else:
             temp_name = name
 
-        initial_table = sg.table(
-            temp_name, catalog=database, quoted=self.compiler.quoted
+        initial_table = sge.Table(
+            this=sg.to_identifier(temp_name, quoted=self.compiler.quoted),
+            catalog=catalog,
+            db=database,
         )
         target = sge.Schema(this=initial_table, expressions=column_defs)
 
@@ -214,7 +231,11 @@ class Backend(SQLBackend, CanCreateDatabase, CanCreateSchema, UrlFromPath):
         )
 
         # This is the same table as initial_table unless overwrite == True
-        final_table = sg.table(name, catalog=database, quoted=self.compiler.quoted)
+        final_table = sge.Table(
+            this=sg.to_identifier(name, quoted=self.compiler.quoted),
+            catalog=catalog,
+            db=database,
+        )
         with self._safe_raw_sql(create_stmt) as cur:
             if query is not None:
                 insert_stmt = sge.insert(query, into=initial_table).sql(self.name)
@@ -254,7 +275,7 @@ class Backend(SQLBackend, CanCreateDatabase, CanCreateSchema, UrlFromPath):
         if temp_memtable_view is not None:
             self.con.unregister(temp_memtable_view)
 
-        return self.table(name, database=database)
+        return self.table(name, database=(catalog, database))
 
     def _load_into_cache(self, name, expr):
         self.create_table(name, expr, schema=expr.schema(), temp=True)
@@ -463,9 +484,8 @@ class Backend(SQLBackend, CanCreateDatabase, CanCreateSchema, UrlFromPath):
         if extensions is not None:
             self._load_extensions(extensions)
 
-        # Default timezone
-        with self._safe_raw_sql("SET TimeZone = 'UTC'"):
-            pass
+        # Default timezone, can't be set with `config`
+        self.settings["timezone"] = "UTC"
 
         self._record_batch_readers_consumed = {}
 
@@ -971,8 +991,8 @@ class Backend(SQLBackend, CanCreateDatabase, CanCreateSchema, UrlFromPath):
         """
         table_loc = self._warn_and_create_table_loc(database, schema)
 
-        catalog = F.current_database()
-        database = F.current_schema()
+        catalog = self.current_catalog
+        database = self.current_database
         if table_loc is not None:
             catalog = table_loc.catalog or catalog
             database = table_loc.db or database
diff --git a/ibis/backends/duckdb/compiler.py b/ibis/backends/duckdb/compiler.py
index d17ba9360..9655086cc 100644
--- a/ibis/backends/duckdb/compiler.py
+++ b/ibis/backends/duckdb/compiler.py
@@ -45,6 +45,7 @@ class DuckDBCompiler(SQLGlotCompiler):
         ops.BitOr: "bit_or",
         ops.BitXor: "bit_xor",
         ops.EndsWith: "suffix",
+        ops.ExtractIsoYear: "isoyear",
         ops.Hash: "hash",
         ops.IntegerRange: "range",
         ops.TimestampRange: "range",
diff --git a/ibis/backends/exasol/compiler.py b/ibis/backends/exasol/compiler.py
index de06d99b0..0940c80a1 100644
--- a/ibis/backends/exasol/compiler.py
+++ b/ibis/backends/exasol/compiler.py
@@ -206,6 +206,9 @@ class ExasolCompiler(SQLGlotCompiler):
     def visit_ExtractWeekOfYear(self, op, *, arg):
         return self.cast(self.f.to_char(arg, "IW"), op.dtype)
 
+    def visit_ExtractIsoYear(self, op, *, arg):
+        return self.cast(self.f.to_char(arg, "IYYY"), op.dtype)
+
     def visit_DayOfWeekName(self, op, *, arg):
         return self.f.concat(
             self.f.substr(self.f.to_char(arg, "DAY"), 0, 1),
diff --git a/ibis/backends/oracle/compiler.py b/ibis/backends/oracle/compiler.py
index 4129aab09..e824ec1d0 100644
--- a/ibis/backends/oracle/compiler.py
+++ b/ibis/backends/oracle/compiler.py
@@ -450,3 +450,6 @@ class OracleCompiler(SQLGlotCompiler):
     def visit_StringConcat(self, op, *, arg):
         any_args_null = (a.is_(NULL) for a in arg)
         return self.if_(sg.or_(*any_args_null), NULL, self.f.concat(*arg))
+
+    def visit_ExtractIsoYear(self, op, *, arg):
+        return self.cast(self.f.to_char(arg, "IYYY"), op.dtype)
diff --git a/ibis/backends/pandas/kernels.py b/ibis/backends/pandas/kernels.py
index d941eb391..460be6ac4 100644
--- a/ibis/backends/pandas/kernels.py
+++ b/ibis/backends/pandas/kernels.py
@@ -440,6 +440,7 @@ serieswise = {
     ops.ExtractSecond: lambda arg: arg.dt.second,
     ops.ExtractWeekOfYear: lambda arg: arg.dt.isocalendar().week.astype("int32"),
     ops.ExtractYear: lambda arg: arg.dt.year,
+    ops.ExtractIsoYear: lambda arg: arg.dt.isocalendar().year,
     ops.IsNull: lambda arg: arg.isnull(),
     ops.NotNull: lambda arg: arg.notnull(),
     ops.Lowercase: lambda arg: arg.str.lower(),
diff --git a/ibis/backends/polars/compiler.py b/ibis/backends/polars/compiler.py
index 840738b25..a793db0c6 100644
--- a/ibis/backends/polars/compiler.py
+++ b/ibis/backends/polars/compiler.py
@@ -1002,6 +1002,7 @@ _date_methods = {
     ops.ExtractDay: "day",
     ops.ExtractMonth: "month",
     ops.ExtractYear: "year",
+    ops.ExtractIsoYear: "iso_year",
     ops.ExtractQuarter: "quarter",
     ops.ExtractDayOfYear: "ordinal_day",
     ops.ExtractWeekOfYear: "week",
diff --git a/ibis/backends/postgres/compiler.py b/ibis/backends/postgres/compiler.py
index 7131f061a..0a6af4b91 100644
--- a/ibis/backends/postgres/compiler.py
+++ b/ibis/backends/postgres/compiler.py
@@ -508,6 +508,9 @@ class PostgresCompiler(SQLGlotCompiler):
     def visit_ExtractWeekOfYear(self, op, *, arg):
         return self.f.extract("week", arg)
 
+    def visit_ExtractIsoYear(self, op, *, arg):
+        return self.f.extract("isoyear", arg)
+
     def visit_ExtractEpochSeconds(self, op, *, arg):
         return self.f.extract("epoch", arg)
 
diff --git a/ibis/backends/snowflake/compiler.py b/ibis/backends/snowflake/compiler.py
index 473918aad..72fcd11c0 100644
--- a/ibis/backends/snowflake/compiler.py
+++ b/ibis/backends/snowflake/compiler.py
@@ -75,6 +75,7 @@ class SnowflakeCompiler(SQLGlotCompiler):
         ops.BitwiseRightShift: "bitshiftright",
         ops.BitwiseXor: "bitxor",
         ops.EndsWith: "endswith",
+        ops.ExtractIsoYear: "yearofweekiso",
         ops.Hash: "hash",
         ops.Median: "median",
         ops.Mode: "mode",
diff --git a/ibis/backends/trino/compiler.py b/ibis/backends/trino/compiler.py
index e53fb7345..7c0c76342 100644
--- a/ibis/backends/trino/compiler.py
+++ b/ibis/backends/trino/compiler.py
@@ -80,6 +80,7 @@ class TrinoCompiler(SQLGlotCompiler):
         ops.ExtractPath: "url_extract_path",
         ops.ExtractFragment: "url_extract_fragment",
         ops.ArrayPosition: "array_position",
+        ops.ExtractIsoYear: "year_of_week",
     }
 
     def _aggregate(self, funcname: str, *args, where):
diff --git a/ibis/expr/operations/temporal.py b/ibis/expr/operations/temporal.py
index a500dd017..c2635661f 100644
--- a/ibis/expr/operations/temporal.py
+++ b/ibis/expr/operations/temporal.py
@@ -108,6 +108,11 @@ class ExtractYear(ExtractDateField):
     pass
 
 
+@public
+class ExtractIsoYear(ExtractDateField):
+    pass
+
+
 @public
 class ExtractMonth(ExtractDateField):
     pass
diff --git a/ibis/expr/types/relations.py b/ibis/expr/types/relations.py
index 29838bfe3..3b9dc3257 100644
--- a/ibis/expr/types/relations.py
+++ b/ibis/expr/types/relations.py
@@ -770,6 +770,12 @@ class Table(Expr, _FixedTextJupyterMixin):
         if isinstance(what, slice):
             limit, offset = util.slice_to_limit_offset(what, self.count())
             return self.limit(limit, offset=offset)
+        # skip the self.bind call for single column access with strings or ints
+        # because dereferencing has significant overhead
+        elif isinstance(what, str):
+            return ops.Field(self.op(), what).to_expr()
+        elif isinstance(what, int):
+            return ops.Field(self.op(), self.columns[what]).to_expr()
 
         args = [
             self.columns[arg] if isinstance(arg, int) else arg
diff --git a/ibis/expr/types/temporal.py b/ibis/expr/types/temporal.py
index 120f59b8e..a63e393d9 100644
--- a/ibis/expr/types/temporal.py
+++ b/ibis/expr/types/temporal.py
@@ -33,6 +33,10 @@ class _DateComponentMixin:
         """Extract the year component."""
         return ops.ExtractYear(self).to_expr()
 
+    def iso_year(self) -> ir.IntegerValue:
+        """Extract the ISO year component."""
+        return ops.ExtractIsoYear(self).to_expr()
+
     def month(self) -> ir.IntegerValue:
         """Extract the month component."""
         return ops.ExtractMonth(self).to_expr()


Test patch:
diff --git a/ibis/backends/duckdb/tests/test_catalog.py b/ibis/backends/duckdb/tests/test_catalog.py
new file mode 100644
index 000000000..a59c04ee3
--- /dev/null
+++ b/ibis/backends/duckdb/tests/test_catalog.py
@@ -0,0 +1,73 @@
+from __future__ import annotations
+
+import pandas as pd
+import pandas.testing as tm
+import pytest
+
+import ibis
+import ibis.common.exceptions as exc
+
+
+@pytest.fixture(scope="session")
+def external_duckdb_file(tmpdir_factory):  # pragma: no cover
+    ddb_path = str(tmpdir_factory.mktemp("data") / "starwars.ddb")
+    con = ibis.duckdb.connect(ddb_path)
+
+    starwars_df = pd.DataFrame(
+        {
+            "name": ["Luke Skywalker", "C-3PO", "R2-D2"],
+            "height": [172, 167, 96],
+            "mass": [77.0, 75.0, 32.0],
+        }
+    )
+    con.create_table("starwars", obj=starwars_df)
+    con.disconnect()
+
+    return ddb_path, starwars_df
+
+
+def test_read_write_external_catalog(con, external_duckdb_file, monkeypatch):
+    monkeypatch.setattr(ibis.options, "default_backend", con)
+
+    ddb_path, starwars_df = external_duckdb_file
+    con.attach(ddb_path, name="ext")
+
+    # Read from catalog
+    assert "ext" in con.list_catalogs()
+    assert "main" in con.list_databases(catalog="ext")
+
+    assert "starwars" in con.list_tables(database="ext.main")
+    assert "starwars" not in con.list_tables()
+
+    starwars = con.table("starwars", database="ext.main")
+    tm.assert_frame_equal(starwars.to_pandas(), starwars_df)
+
+    # Write to catalog
+    t = ibis.memtable([{"a": 1, "b": "foo"}, {"a": 2, "b": "baz"}])
+
+    _ = con.create_table("t2", obj=t, database="ext.main")
+
+    assert "t2" in con.list_tables(database="ext.main")
+    assert "t2" not in con.list_tables()
+
+    table = con.table("t2", database="ext.main")
+
+    tm.assert_frame_equal(t.to_pandas(), table.to_pandas())
+
+    # Overwrite table in catalog
+
+    t_overwrite = ibis.memtable([{"a": 8, "b": "bing"}, {"a": 9, "b": "bong"}])
+
+    _ = con.create_table("t2", obj=t_overwrite, database="ext.main", overwrite=True)
+
+    assert "t2" in con.list_tables(database="ext.main")
+    assert "t2" not in con.list_tables()
+
+    table = con.table("t2", database="ext.main")
+
+    tm.assert_frame_equal(t_overwrite.to_pandas(), table.to_pandas())
+
+
+def test_raise_if_catalog_and_temp(con):
+    with pytest.raises(exc.UnsupportedArgumentError):
+        con.create_table("some_table", obj="hi", temp=True, database="ext.main")
diff --git a/ibis/backends/duckdb/tests/test_client.py b/ibis/backends/duckdb/tests/test_client.py
index e5519edc8..6665615a2 100644
--- a/ibis/backends/duckdb/tests/test_client.py
+++ b/ibis/backends/duckdb/tests/test_client.py
@@ -297,3 +297,10 @@ def test_list_tables_schema_warning_refactor(con):
 
     assert con.list_tables(database="shops") == icecream_table
     assert con.list_tables(database=("shops",)) == icecream_table
+
+
+def test_settings_repr():
+    con = ibis.duckdb.connect()
+    view = repr(con.settings)
+    assert "name" in view
+    assert "value" in view
diff --git a/ibis/backends/tests/test_temporal.py b/ibis/backends/tests/test_temporal.py
index be6b559a3..b3fb08dd9 100644
--- a/ibis/backends/tests/test_temporal.py
+++ b/ibis/backends/tests/test_temporal.py
@@ -10,6 +10,7 @@ import numpy as np
 import pandas as pd
 import pytest
 import sqlglot as sg
+import toolz
 from pytest import param
 
 import ibis
@@ -99,6 +100,45 @@ def test_timestamp_extract(backend, alltypes, df, attr):
     backend.assert_series_equal(result, expected)
 
 
+@pytest.mark.parametrize(
+    "transform", [toolz.identity, methodcaller("date")], ids=["timestamp", "date"]
+)
+@pytest.mark.notimpl(
+    ["druid"],
+    raises=(AttributeError, com.OperationNotDefinedError),
+    reason="AttributeError: 'StringColumn' object has no attribute 'X'",
+)
+@pytest.mark.notyet(
+    ["mysql", "sqlite", "mssql", "impala", "datafusion", "pyspark", "flink"],
+    raises=com.OperationNotDefinedError,
+    reason="backend doesn't appear to support this operation directly",
+)
+def test_extract_iso_year(backend, con, alltypes, df, transform):
+    value = transform(alltypes.timestamp_col)
+    name = "iso_year"
+    expr = value.iso_year().name(name)
+    result = expr.execute()
+    expected = backend.default_series_rename(
+        df.timestamp_col.dt.isocalendar().year.astype("int32")
+    ).rename(name)
+    backend.assert_series_equal(result, expected)
+
+
+@pytest.mark.notimpl(
+    ["druid"],
+    raises=(AttributeError, com.OperationNotDefinedError),
+    reason="AttributeError: 'StringColumn' object has no attribute 'X'",
+)
+@pytest.mark.notyet(
+    ["mysql", "sqlite", "mssql", "impala", "datafusion", "pyspark", "flink"],
+    raises=com.OperationNotDefinedError,
+    reason="backend doesn't appear to support this operation directly",
+)
+def test_iso_year_does_not_match_date_year(con):
+    expr = ibis.date("2022-01-01").iso_year()
+    assert con.execute(expr) == 2021
+
+
 @pytest.mark.parametrize(
     ("func", "expected"),
     [
diff --git a/ibis/tests/benchmarks/test_benchmarks.py b/ibis/tests/benchmarks/test_benchmarks.py
index a10070959..0ef25d0a1 100644
--- a/ibis/tests/benchmarks/test_benchmarks.py
+++ b/ibis/tests/benchmarks/test_benchmarks.py
@@ -6,6 +6,7 @@ import inspect
 import itertools
 import os
 import string
+from operator import attrgetter, itemgetter
 
 import numpy as np
 import pandas as pd
@@ -832,3 +833,17 @@ def test_big_expression_compile(benchmark):
     t2 = clean_names(t)
 
     assert benchmark(ibis.to_sql, t2, dialect="duckdb")
+
+
+@pytest.fixture(scope="module")
+def many_cols():
+    return ibis.table({f"x{i:d}": "int" for i in range(10000)}, name="t")
+
+
+@pytest.mark.parametrize(
+    "getter",
+    [itemgetter("x0"), itemgetter(0), attrgetter("x0")],
+    ids=["str", "int", "attr"],
+)
+def test_column_access(benchmark, many_cols, getter):
+    benchmark(getter, many_cols)