More From The Duck Pond - ArcGIS ProでのDuckDBの使用

bharold

以前の投稿では、ArcGIS OnlineのホストされたノートブックでDuckDBを使い始めました。  その投稿では、GeoParquetファイルをobject storeで読み取り、フィーチャ サービスを維持する方法を紹介しました。  これは強力なワークフローですが、日常的には、取り込みに非常に時間がかかる一般的なファイル形式、例えばCSV、Excel、JSONなどを扱うことが多いでしょう。  本投稿は、DuckDBがあらゆるファイル形式にSQLを可能にし、さらにEsri形式への簡単な変換もサポートすることを示し、あなたの作業を楽にすることについてです。  基本的なSQLとPythonのスキルがあれば、このサンプルとウェブヘルプを読めば問題ありません。

こちらは実際の例です。Bloomington Indianaのオープン311インシデントが数秒でフィーチャクラスに移行されます。

Bloomington 311 Incidents

 

このノートブック（ブログダウンロード内）は、公開URLのCSVファイルを読み込み、プロジェクトのデフォルトジオデータベースにフィーチャクラスを書き込みます。  ノートブックはスクリプトツールと比べて開始に適しています。セル内で対話的にSQLを試行錯誤できるためです。  動作が確認できたら、コードをスクリプトツールにコピーしてスケジュールによる自動化が簡単になります。

しかしまずはDuckDBをインストールする必要があります！  Pythonパッケージを環境に追加する通常の方法は、arcgispro-py3環境をクローンし、その後ProのバックステージのPackage Manager UIでパッケージを追加することです。  パッケージ名はpython-duckdbです。  私の場合はエラーになったので（よくあることです）、手動インストールしました。  デフォルト環境をarcgispro-py3-quackという名前でクローンし、ArcGISプログラムグループからPythonコマンドプロンプトを開いて次のコマンド（'username'はあなたのユーザー名）を実行しました：

 

conda install -c conda-forge python-duckdb=1.0.0 -p "C:\Users\username\AppData\Local\ESRI\conda\envs\arcgispro-py3-quack" --yes

 

最新のDuckDB配布版（執筆時点では1.0.0）を必ず入手してください。

それでは各ノートブックセルを見ていきましょう。最初はかなりシンプルで、インポートとduckdb呼び出しによる環境設定です。

 

# インポートと環境設定

import arcpy
import duckdb
from arcgis.features import GeoAccessor, GeoSeriesAccessor
import os
import requests
from urllib.parse import urlparse
conn = duckdb.connect()
conn.sql("install spatial;load spatial;")
conn.sql("install httpfs;load httpfs;")
conn.sql("set s3_region='us-west-2';")
conn.sql("set enable_object_cache=true;")
arcpy.env.overwriteOutput = True

 

2番目のセルではCSVデータを読み込みながら自動的にデータ型推論してpandasデータフレームを作成します。非常に助かります。  アクセス先サーバーがDuckDBが望むHTTPレンジリクエストアクセスをサポートしていない場合に備え、防御的にコードを書いています。この場合はデータをダウンロードしました。

 

# duckdbへデータ読み込み

url = r'https://data.bloomington.in.gov/resource/aw6y-t4ix.csv?$limit=200000'
filename = os.path.basename(urlparse(url).path)
base, extension = os.path.splitext(filename)
try:
    sql = "create or replace view bloomington311_view as select * from read_csv_auto('{}');".format(url)
    conn.sql(sql)
except:
    response = requests.get(url)
    with open(filename, 'w', encoding="utf-8") as f:
        f.write(response.text)
    f.close()
    sql = "create or replace view bloomington311_view as select * from read_csv_auto('{}');".format(filename)
    conn.sql(sql)
# SQLで必要なクエリを書いて名前変更やキャストなど処理します。
# このデータセットにはすでにWKT列がありますが、もしなければ
# DuckDBのST_関数で作成してください。
sql = """select service_request_id, requested_datetime, updated_datetime, closed_date, status_description, source,
service_name, description, agency_responsible, address, city, state, try_cast (zip as varchar(10)) as zip, sladays,
request_complete_days, sla_diff_days,
geocoded_column as SHAPE from bloomington311_view where SHAPE is not null;"""
df = conn.sql(sql).df()

 

ジオメトリ用WKT値の使用または作成について注意してください。 DuckDBの空間拡張機能には豊富な空間関数があります。

次にデータフレームをフィーチャクラスに変換します.  他にも変換オプションがあります. arcgis Python APIはデータフレームの由来には無関心なので—DuckDBでも問題ありません！—空間情報を有効化してからジオデータベースフィーチャクラスへ書き込みます。属性フィールドプロパティの調整は不要で、そのまま機能します（上記SQL参照。ZIPコードをテキストへキャストする典型的なケースも含みます）。

 

# フィーチャクラスを書き出す

df.spatial.set_geometry("SHAPE",sr=4326,inplace=True)
aprx = arcpy.mp.ArcGISProject("CURRENT")
gdb = aprx.defaultGeodatabase
out_fc = arcpy.ValidateTableName(base,gdb)
location = os.path.join(gdb,out_fc)
df.spatial.to_featureclass(location)

 

最後にお知らせ！

 

print("Created feature class {}".format(location))

 

以上です。DuckDBのおかげで高速かつシンプルでスマートなデータ移行が実現しました！

ノートブックはブログダウンロード内にありますので、ご利用状況をお知らせください。

MatejVrtich

Hi @BruceHarold,

Thank you for all the great articles you’ve written about ETL, they’ve been really inspiring.

This particular one encouraged me to dive into the duck, especially with the recent addition of DuckDB support in the default Python environments in both ArcGIS Pro and ArcGIS Server.

I wanted to share some of my own experiments and created this little “Quack” Python script tool just for fun (see attached). It's using DuckDB to Arrow to ArcGIS pipeline as I wanted to investigate the Arrow support in ArcGIS as well.

The idea behind the tool is to be shared as a Web Tool in ArcGIS Enterprise and to offer an open way to load data into an app as an in-memory feature layer or table, with an option to store the result as a hosted feature layer as well (handled by the Web Tool). Both Map Viewer and Experience Builder can work directly with in-memory data, which provides a great UX for using the Web Tool itself.

So, have fun — and please keep publishing 🙂

Regards,
Matej

I paste it here as I do not see the attach file button, sorry 🙂

import duckdb
import arcpy
import pyarrow as pa

def add_esri_metadata(arrow_table: pa.Table, wkid: int = 4326) -> pa.Table:
    """Attach ArcGIS-compatible geometry metadata."""
    sr = arcpy.SpatialReference(wkid)
    wkt = sr.exportToString()

    new_fields = []
    for field in arrow_table.schema:
        metadata = {
            k if isinstance(k, bytes) else k.encode("utf-8"): (
                v if isinstance(v, bytes) else v.encode("utf-8")
            )
            for k, v in (field.metadata or {}).items()
        }

        if field.name == "geometry":
            metadata[b"esri.encoding"] = (
                b"WKB"  # TODO: add support for more encodings
            )
            metadata[b"esri.sr_wkt"] = wkt.encode("utf-8")

        if field.name == "OBJECTID":
            metadata[b"esri.oid"] = b"esri.int64"

        new_fields.append(pa.field(field.name, field.type, metadata=metadata))

    return pa.Table.from_arrays(
        arrow_table.columns,
        schema=pa.schema(new_fields, metadata=arrow_table.schema.metadata),
    )


def quack(sql, result="memory/quack"):

    con = duckdb.connect(":memory:")

    for ext in ("httpfs", "spatial"):
        con.execute(f"INSTALL {ext};")
        con.execute(f"LOAD {ext};")

    # Execute query and get Arrow table
    arr = con.execute(
        f"""
    {sql}
    """
    ).arrow()

    # Check if any geometry column exists
    has_geometry = False
    geometry_types = ["geometry", "geom", "shape", "wkb_geometry", "the_geom"]

    # Check column names (case-insensitive)
    column_names = [col.lower() for col in arr.column_names]

    # Check for geometry column by name
    for geom_type in geometry_types:
        if geom_type in column_names:
            has_geometry = True
            break

    # Also check schema for spatial types (DuckDB spatial extension)
    if not has_geometry:
        for field in arr.schema:
            field_type_str = str(field.type).lower()
            if any(
                spatial_type in field_type_str
                for spatial_type in [
                    "geometry",
                    "point",
                    "linestring",
                    "polygon",
                    "multipoint",
                    "multilinestring",
                    "multipolygon",
                    "geometrycollection",
                ]
            ):
                has_geometry = True
                break

    if arcpy.Exists(result):
        arcpy.AddMessage(f"Output {result} already exists - using Delete")
        arcpy.management.Delete(result)

    # Use appropriate tool based on geometry presence
    if has_geometry:
        arcpy.AddMessage("Geometry detected - using CopyFeatures")
        arcpy.management.CopyFeatures(add_esri_metadata(arr), result)
    else:
        arcpy.AddMessage("No geometry detected - using CopyRows")
        arcpy.management.CopyRows(arr, result)

    con.close()
    return result


if __name__ == "__main__":

    sql = arcpy.GetParameterAsText(0)
    result = arcpy.GetParameterAsText(1)

    result = quack(sql, result)

    arcpy.SetParameterAsText(1, result)

 

Edit: I should warn you against exposing the SQL parameter this way (without restrictions) in a Web Tool/GP Service if sharing to ArcGIS Enterprise, as it gives a lot of power if directly passed into the execute function. You can even list files from a file system from inside the ArcSOC.exe running the tool with SELECT * FROM glob('*'); or even return the contents of files themselves with SELECT string_agg(content, '\n') AS file_content FROM read_text(filename);