Mehr vom Duck Pond - Verwendung von DuckDB in ArcGIS Pro

bharold

In einem früheren Beitrag habe ich erste Erfahrungen mit DuckDB in einem gehosteten Notebook in ArcGIS Online gesammelt.  Dieser Beitrag hob das Lesen von GeoParquet-Dateien in einem object store hervor, um einen Feature-Service aufrechtzuerhalten.  Das ist ein leistungsstarker Workflow, aber es ist viel wahrscheinlicher, dass Ihre tägliche Arbeit das Verarbeiten gängiger Dateiformate umfasst, die viel zu viel Zeit für das Einlesen benötigen – wie CSV, Excel und JSON.  Dieser Beitrag zeigt, wie Sie Ihr Leben erleichtern können, indem er zeigt, wie DuckDB SQL für jedes Dateiformat ermöglicht und auch einfache Konvertierung in Esri-Formate unterstützt.  Wenn Sie grundlegende SQL- und Python-Kenntnisse haben – also wenn Sie dieses Beispiel und die Webhilfe lesen können – werden Sie keine Probleme haben.<\/P>

Hier ist etwas Live-Action: Bloomington Indiana offene 311-Vorfälle, die in Sekunden in eine Feature-Class migriert wurden.<\/P>

Bloomington 311 Incidents<\/span><\/P> <\/P>Das Notebook (im Blog-Download) liest eine CSV-Datei von einer öffentlichen URL und schreibt eine Feature-Class in die Standard-Geodatabase des Projekts.  Notebooks sind ein guter Einstieg im Vergleich zu einem Skript-Tool, da Sie interaktiv in einer Zelle arbeiten können, während Sie Ihr SQL entwickeln.  Sobald alles funktioniert, könnten Sie den Code in ein Skript-Tool kopieren, um eine einfache Automatisierung nach Zeitplan zu ermöglichen.<\/P>Aber zuerst müssen Sie DuckDB installieren!  Der übliche Weg, ein Python-Paket zu Ihrer Umgebung hinzuzufügen, besteht darin, die arcgispro-py3-Umgebung zu klonen und dann ein Paket im Package Manager UI backstage in Pro hinzuzufügen.  Sie würden nach einem Paket namens python-duckdb suchen.  Das führte bei mir zu einem Fehler (das passiert), also habe ich eine manuelle Installation durchgeführt.  Ich habe meine Standardumgebung auf eine namens arcgispro-py3-quack geklont und DuckDB installiert, indem ich die Python-Befehlszeile aus der ArcGIS-Programmgruppe geöffnet und diesen Befehl ausgeführt habe ('username' wird durch Ihren Benutzernamen ersetzt):<\/P> <\/P>conda install -c conda-forge python-duckdb=1.0.0 -p "C:\Users\username\AppData\Local\ESRI\conda\envs\arcgispro-py3-quack" --yes<\/pre> <\/P>Stellen Sie sicher, dass Sie die neueste DuckDB-Version erhalten, aktuell ist es 1.0.0.<\/P>Lassen Sie uns jede Notebook-Zelle durchgehen.  Die erste ist ziemlich einfach: die Importe und einige Aufrufe an duckdb zur Einrichtung der Umgebung.<\/P> <\/P># Importe, Umgebung 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<\/pre> <\/P>Die zweite Zelle erstellt ein pandas DataFrame durch Lesen der CSV-Daten mit automatischer Typenerkennung – eine große Hilfe.  Sie werden bemerken, dass ich defensiv programmiere für den Fall, dass der Server, den ich anfrage, keinen HTTP Range Request-Zugriff unterstützt wie von DuckDB gewünscht – ich lade die Daten herunter – was in diesem Fall notwendig war.<\/P> <\/P># Daten in duckdb lesen 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) # Erstellen Sie die gewünschte SQL-Abfrage zum Umbenennen, Casten oder anderweitigen Verarbeiten der Daten. # Dieses Dataset hat bereits eine Spalte im WKT-Format. Wenn Ihre Daten das nicht haben, # müssen Sie eine mit den ST_-Funktionen in DuckDB erstellen. 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()<\/pre> <\/P>Beachten Sie den Hinweis zur Verwendung oder Erstellung von WKT-Werten für Ihre Geometrie.  Die räumliche Erweiterung für DuckDB bietet eine umfangreiche Sammlung räumlicher Funktionen.<\/P>Jetzt konvertieren wir das DataFrame in eine Feature-Class.  Sie haben weitere Konvertierungsoptionen. Beachten Sie, dass die arcgis Python API keine Ahnung hat, woher das DataFrame stammt – DuckDB funktioniert hier einwandfrei! – daher aktiviere ich das DataFrame räumlich und schreibe es dann in eine Geodatabase-Feature-Class.  Kein Aufwand bei der Anpassung der Attributfeld-Eigenschaften nötig, sie funktionieren einfach (siehe das obige SQL-Beispiel; das klassische Problem des Castens von Postleitzahlen zu Text trat auf).<\/P> <\/P># Schreibe die Feature-Class 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)<\/pre> <\/P>Zum Schluss noch eine Ankündigung!<\/P> <\/P>print("Feature-Class {} erstellt.".format(location))<\/pre> <\/P>Das war's – schnelle, einfache und intelligente Datenmigration mit etwas Hilfe von DuckDB!<\/P>Das Notebook ist im Blog-Download enthalten. Lassen Sie uns wissen, wie es bei Ihnen läuft.<\/P>

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);