Using filepath() in synapse

Question

Using filepath() in synapse

Haoran Bai 80

I created an extrenal table in synapse and try to use filepath function on it, but got below error:

User's image

Here is my table:

CREATE EXTERNAL TABLE bronze.trip_data_green_csv
    (
        VendorID INT,
        lpep_pickup_datetime datetime2(7),
        lpep_dropoff_datetime datetime2(7),
        store_and_fwd_flag CHAR(1),
        RatecodeID INT,
        PULocationID INT,
        DOLocationID INT,
        passenger_count INT,
        trip_distance FLOAT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        ehail_fee INT,
        improvement_surcharge FLOAT,
        total_amount FLOAT,
        payment_type INT,
        trip_type INT,
        congestion_surcharge FLOAT
    )  
    WITH (
        LOCATION = 'nyc-taxi-data/raw/trip_data_green_csv/year=*/month=*/green_tripdata_*.csv',
        DATA_SOURCE = nyc_taxi_src, 
        FILE_FORMAT = csv_file_format
    );

And here is my file format:

CREATE EXTERNAL FILE FORMAT csv_file_format
WITH
(  
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
      FIELD_TERMINATOR = ',', 
      FIRST_ROW = 2, 
      USE_TYPE_DEFAULT = FALSE,
      ENCODING = 'UTF8', 
      PARSER_VERSION = '2.0'
    )
);

J N S S Kasyap 3,300 Reputation points Microsoft External Staff Moderator

2025-05-30T08:41:55.4266667+00:00
Hi @Haoran Bai

Filepath function prefix 'result' does not match the alias name or function argument is out of range.

The above error means that you're using filepath() incorrectly in your SELECT statement for an external table in Synapse.

filepath() is only supported on views or queries that directly reference files, not on external tables.
External tables are metadata-bound representations of files, and they don’t expose internal metadata like the actual file path per row. filepath() is only usable when querying raw files using OPENROWSET.
This means you cannot use filepath() on an external table alias like this:

SELECT result.filepath(), result.* FROM bronze.trip_data_green_csv AS result

If you want to get the file path, use a SELECT directly on the file location via OPENROWSET, like this:

SELECT filepath() AS [FilePath], * FROM OPENROWSET( BULK 'nyc-taxi-data/raw/trip_data_green_csv/year=*/month=*/green_tripdata_*.csv', DATA_SOURCE = 'nyc_taxi_src', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIRSTROW = 2 ) AS [rows]

This will work correctly and return the file path for each row

I hope this helps! Feel free to reach out if you have any more questions or need further clarification.

If you found the answer useful, kindly consider upvoting and marking it as accepted to help others as well
Haoran Bai 80 Reputation points

2025-06-02T05:39:11.19+00:00

Thank you for your explanation, but what should I do if I want get the file path Metadata for external tables?
J N S S Kasyap 3,300 Reputation points Microsoft External Staff Moderator

2025-06-04T10:57:33.22+00:00
@Haoran Bai
In addition to @Nandan Hegde point

you can get the defined file path pattern (i.e., the static path or wildcard path used when creating the external table) from system views like:

SELECT et.name AS ExternalTableName, el.location AS FilePathPattern FROM sys.external_tables et JOIN sys.external_locations el ON et.data_source_id = el.data_source_id;

OR if using sys.external_data_sources:

SELECT t.name AS TableName, ds.location AS DataSourceLocation FROM sys.external_tables t JOIN sys.external_data_sources ds ON t.data_source_id = ds.data_source_id;

Use sys.external_tables and related views to get the defined file path pattern used in the table's LOCATION clause.

If you want the actual file path per row, you must use OPENROWSET with filepath().

I hope this helps! Feel free to reach out if you have any more questions or need further clarification. If you found the answer useful, kindly consider upvoting and marking it as accepted to help others as well
J N S S Kasyap 3,300 Reputation points Microsoft External Staff Moderator

2025-06-05T08:17:24.67+00:00

@Haoran Bai
If the latest comment your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Accepted answer

0 additional answers

Your answer

J N S S Kasyap 3,300 Reputation points Microsoft External Staff Moderator

2025-05-30T08:41:55.4266667+00:00

Hi @Haoran Bai

Filepath function prefix 'result' does not match the alias name or function argument is out of range.

The above error means that you're using filepath() incorrectly in your SELECT statement for an external table in Synapse.

filepath() is only supported on views or queries that directly reference files, not on external tables.
External tables are metadata-bound representations of files, and they don’t expose internal metadata like the actual file path per row. filepath() is only usable when querying raw files using OPENROWSET.
This means you cannot use filepath() on an external table alias like this:

SELECT result.filepath(), result.* FROM bronze.trip_data_green_csv AS result

If you want to get the file path, use a SELECT directly on the file location via OPENROWSET, like this:

SELECT filepath() AS [FilePath], * FROM OPENROWSET( BULK 'nyc-taxi-data/raw/trip_data_green_csv/year=*/month=*/green_tripdata_*.csv', DATA_SOURCE = 'nyc_taxi_src', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIRSTROW = 2 ) AS [rows]

This will work correctly and return the file path for each row

I hope this helps! Feel free to reach out if you have any more questions or need further clarification.

If you found the answer useful, kindly consider upvoting and marking it as accepted to help others as well
Haoran Bai 80 Reputation points

2025-06-02T05:39:11.19+00:00

Thank you for your explanation, but what should I do if I want get the file path Metadata for external tables?
J N S S Kasyap 3,300 Reputation points Microsoft External Staff Moderator

2025-06-04T10:57:33.22+00:00

@Haoran Bai
In addition to @Nandan Hegde point

you can get the defined file path pattern (i.e., the static path or wildcard path used when creating the external table) from system views like:

SELECT et.name AS ExternalTableName, el.location AS FilePathPattern FROM sys.external_tables et JOIN sys.external_locations el ON et.data_source_id = el.data_source_id;

OR if using sys.external_data_sources:

SELECT t.name AS TableName, ds.location AS DataSourceLocation FROM sys.external_tables t JOIN sys.external_data_sources ds ON t.data_source_id = ds.data_source_id;

Use sys.external_tables and related views to get the defined file path pattern used in the table's LOCATION clause.

If you want the actual file path per row, you must use OPENROWSET with filepath().

I hope this helps! Feel free to reach out if you have any more questions or need further clarification. If you found the answer useful, kindly consider upvoting and marking it as accepted to help others as well
J N S S Kasyap 3,300 Reputation points Microsoft External Staff Moderator

2025-06-05T08:17:24.67+00:00

@Haoran Bai
If the latest comment your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Nandan Hegde 35,831 MVP Volunteer Moderator

You can use Select Location from sys.external_tables to get the file path details of external tables

Share via

Using filepath() in synapse

0 additional answers

Your answer