René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Entity SQL does not work as documented with PostgreSQL Offline Store","articleBody":"## Expected Behavior \n\nEntity SQL as documented in [Example: entity SQL query for generating training data\n](https://docs.feast.dev/getting-started/concepts/feature-retrieval?q=PostgreSQLSource#example-entity-sql-query-for-generating-training-data) works.\n\n```\nentity_sql = f\"\"\"\n    SELECT\n        entity_id,\n        event_timestamp\n    FROM {feature_store.get_data_source(\"customer_credit_risk_feature_source\").get_table_query_string()} \n\"\"\"\nprint(entity_sql)\n-----\n    SELECT\n        entity_id,\n        event_timestamp\n    FROM (SELECT * FROM credit.customer_credit_risk_features) \n```\n```\ntraining_df = feature_store.get_historical_features(\n    entity_df=entity_sql,\n    features=customer_credit_risk_features,\n).to_df()\n```\n\n\n## Current Behavior\n\n```\n---------------------------------------------------------------------------\nSyntaxError                               Traceback (most recent call last)\nFile ~/venv/feast/lib/python3.11/site-packages/pandas/io/sql.py:2664, in SQLiteDatabase.execute(self, sql, params)\n   2663 try:\n-\u003e 2664     cur.execute(sql, *args)\n   2665     return cur\n\nFile ~/venv/feast/lib/python3.11/site-packages/psycopg/cursor.py:97, in Cursor.execute(self, query, params, prepare, binary)\n     96 except e._NO_TRACEBACK as ex:\n---\u003e 97     raise ex.with_traceback(None)\n     98 return self\n\nSyntaxError: subquery in FROM must have an alias\nLINE 5:     FROM (SELECT * FROM credit.customer_credit_risk_features...\n                 ^\nHINT:  For example, FROM (SELECT ...) [AS] foo.\n\nThe above exception was the direct cause of the following exception:\n\nDatabaseError                             Traceback (most recent call last)\nCell In[55], line 1\n----\u003e 1 training_df = feature_store.get_historical_features(\n      2     entity_df=entity_sql,\n      3     features=customer_credit_risk_features,\n      4 ).to_df()\n\nFile ~/venv/feast/lib/python3.11/site-packages/feast/feature_store.py:1207, in FeatureStore.get_historical_features(self, entity_df, features, full_feature_names, start_date, end_date)\n   1204 if end_date is not None:\n   1205     kwargs[\"end_date\"] = end_date\n-\u003e 1207 job = provider.get_historical_features(\n   1208     self.config,\n   1209     feature_views,\n   1210     _feature_refs,\n   1211     entity_df,\n   1212     self._registry,\n   1213     self.project,\n   1214     full_feature_names,\n   1215     **kwargs,\n   1216 )\n   1218 return job\n\nFile ~/venv/feast/lib/python3.11/site-packages/feast/infra/passthrough_provider.py:467, in PassthroughProvider.get_historical_features(self, config, feature_views, feature_refs, entity_df, registry, project, full_feature_names, **kwargs)\n    456 def get_historical_features(\n    457     self,\n    458     config: RepoConfig,\n   (...)    465     **kwargs,\n    466 ) -\u003e RetrievalJob:\n--\u003e 467     job = self.offline_store.get_historical_features(\n    468         config=config,\n    469         feature_views=feature_views,\n    470         feature_refs=feature_refs,\n    471         entity_df=entity_df,\n    472         registry=registry,\n    473         project=project,\n    474         full_feature_names=full_feature_names,\n    475         **kwargs,\n    476     )\n    478     return job\n\nFile ~/venv/feast/lib/python3.11/site-packages/feast/infra/offline_stores/contrib/postgres_offline_store/postgres.py:170, in PostgreSQLOfflineStore.get_historical_features(config, feature_views, feature_refs, entity_df, registry, project, full_feature_names, **kwargs)\n    160         start_date = make_tzaware(start_date)\n    162     entity_df = pd.DataFrame(\n    163         {\n    164             \"event_timestamp\": pd.date_range(\n   (...)    167         }\n    168     )\n--\u003e 170 entity_schema = _get_entity_schema(entity_df, config)\n    172 entity_df_event_timestamp_col = (\n    173     offline_utils.infer_event_timestamp_from_entity_df(entity_schema)\n    174 )\n    176 entity_df_event_timestamp_range = _get_entity_df_event_timestamp_range(\n    177     entity_df,\n    178     entity_df_event_timestamp_col,\n    179     config,\n    180 )\n\nFile ~/venv/feast/lib/python3.11/site-packages/feast/infra/offline_stores/contrib/postgres_offline_store/postgres.py:503, in _get_entity_schema(entity_df, config)\n    501 elif isinstance(entity_df, str):\n    502     df_query = f\"({entity_df}) AS sub\"\n--\u003e 503     return get_query_schema(config.offline_store, df_query)\n    504 else:\n    505     raise InvalidEntityType(type(entity_df))\n\nFile ~/venv/feast/lib/python3.11/site-packages/feast/infra/utils/postgres/connection_utils.py:116, in get_query_schema(config, sql_query)\n    114 with _get_conn(config) as conn:\n    115     conn.read_only = True\n--\u003e 116     df = pd.read_sql(\n    117         f\"SELECT * FROM {sql_query} LIMIT 0\",\n    118         conn,\n    119     )\n    120     return dict(zip(df.columns, df.dtypes))\n\nFile ~/venv/feast/lib/python3.11/site-packages/pandas/io/sql.py:708, in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize, dtype_backend, dtype)\n    706 with pandasSQL_builder(con) as pandas_sql:\n    707     if isinstance(pandas_sql, SQLiteDatabase):\n--\u003e 708         return pandas_sql.read_query(\n    709             sql,\n    710             index_col=index_col,\n    711             params=params,\n    712             coerce_float=coerce_float,\n    713             parse_dates=parse_dates,\n    714             chunksize=chunksize,\n    715             dtype_backend=dtype_backend,\n    716             dtype=dtype,\n    717         )\n    719     try:\n    720         _is_table_name = pandas_sql.has_table(sql)\n\nFile ~/venv/feast/lib/python3.11/site-packages/pandas/io/sql.py:2728, in SQLiteDatabase.read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend)\n   2717 def read_query(\n   2718     self,\n   2719     sql,\n   (...)   2726     dtype_backend: DtypeBackend | Literal[\"numpy\"] = \"numpy\",\n   2727 ) -\u003e DataFrame | Iterator[DataFrame]:\n-\u003e 2728     cursor = self.execute(sql, params)\n   2729     columns = [col_desc[0] for col_desc in cursor.description]\n   2731     if chunksize is not None:\n\nFile ~/venv/feast/lib/python3.11/site-packages/pandas/io/sql.py:2676, in SQLiteDatabase.execute(self, sql, params)\n   2673     raise ex from inner_exc\n   2675 ex = DatabaseError(f\"Execution failed on sql '{sql}': {exc}\")\n-\u003e 2676 raise ex from exc\n\nDatabaseError: Execution failed on sql 'SELECT * FROM (\n    SELECT\n        entity_id,\n        event_timestamp\n    FROM (SELECT * FROM credit.customer_credit_risk_features) \n) AS sub LIMIT 0': subquery in FROM must have an alias\nLINE 5:     FROM (SELECT * FROM credit.customer_credit_risk_features...\n                 ^\nHINT:  For example, FROM (SELECT ...) [AS] foo.\n```\n\n## Steps to reproduce\n\n### Specifications\n\n- Version: 0.53\n- Platform: MacOSX\n- Subsystem: Python 3.11\n\n## Possible Solution\n\n```\nentity_sql = f\"\"\"\n    SELECT\n        entity_id,\n        event_timestamp\n    FROM {feature_store.get_data_source(\"customer_credit_risk_feature_source\").get_table_query_string()} AS ENTITY_SQL\n\"\"\"\nprint(entity_sql)\n```\n\n","author":{"url":"https://github.com/oonisim","@type":"Person","name":"oonisim"},"datePublished":"2025-09-10T07:10:26.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/5605/feast/issues/5605"}