Meer van The Duck Pond - DuckDB gebruiken in ArcGIS Pro

bharold

In een eerder bericht heb ik mijn eerste ervaring opgedaan met het gebruik van DuckDB in een gehoste notebook in ArcGIS Online.  Dat bericht benadrukte het lezen van GeoParquet-bestanden in een object store om een feature service te onderhouden.  Dat is een krachtige workflow, maar het is veel waarschijnlijker dat je dagelijkse werk bestaat uit het verwerken van gangbare bestandsformaten die veel te veel tijd kosten om te importeren - zoals CSV, Excel en JSON.  Dit bericht gaat over het makkelijker maken van je leven door te laten zien hoe DuckDB SQL mogelijk maakt voor elk bestandsformaat en ook eenvoudige conversie naar Esri-formaten ondersteunt.  Als je basis SQL- en Python-vaardigheden hebt - zoals dat je deze voorbeeldcode en de webhulp kunt lezen - dan komt het goed.<\/P>

Hier is wat live actie, Bloomington Indiana open 311 incidenten gemigreerd naar een feature class in enkele seconden.<\/P>

Bloomington 311 Incidenten<\/span><\/P> <\/P>De notebook (in de blog download) leest een CSV-bestand vanaf een openbare URL en schrijft een feature class naar de standaard geodatabase van het project.  Notebooks zijn een goede plek om te beginnen vergeleken met een script tool omdat je interactief in een cel kunt werken terwijl je je SQL uitzoekt.  Zodra alles werkt, kun je de code kopiëren naar een script tool voor eenvoudige automatisering volgens schema.<\/P>Maar eerst moet je DuckDB installeren!  De gebruikelijke manier om een Python-pakket aan je omgeving toe te voegen is door de arcgispro-py3 omgeving te klonen en vervolgens een pakket toe te voegen via de Package Manager UI backstage in Pro.  Je zou zoeken naar een pakket genaamd python-duckdb.  Dit gaf bij mij een foutmelding (dat kan gebeuren), dus heb ik handmatig geïnstalleerd.  Ik kloonde mijn standaardomgeving naar eentje die ik arcgispro-py3-quack noemde en installeerde DuckDB door de Python Command Prompt te openen vanuit de ArcGIS-programmagroep en vervolgens dit commando uit te voeren ('username' wordt jouw gebruikersnaam):<\/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<\/code><\/pre> <\/P>Zorg ervoor dat je de nieuwste DuckDB distributie haalt, op het moment van schrijven is dat 1.0.0.<\/P>Laten we elke notebook-cel doornemen.  De eerste is vrij eenvoudig, de imports en enkele aanroepen naar duckdb om de omgeving op te zetten.<\/P> <\/P># Imports, omgeving

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<\/code><\/pre> <\/P>De tweede cel maakt een pandas dataframe door de CSV-gegevens te lezen terwijl automatisch datatypes worden afgeleid - zo'n grote hulp.  Je zult merken dat ik defensief codeer voor het geval de server die ik aanspreek geen HTTP range request toegang ondersteunt zoals DuckDB wil - ik download dan de data - wat in dit geval nodig was.<\/P> <\/P># Lees data in 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)
# Bouw de query die je wilt in SQL om kolomnamen te wijzigen, casten of anderszins de data te verwerken.
# Deze dataset heeft al een kolom in WKT, als jouw data dat niet heeft dan moet je er één maken met behulp van de ST_ functies in DuckDB.
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()<\/code><\/pre> <\/P>Let op het punt over het gebruiken of creëren van WKT-waarden voor je geometrie.  De spatial extensie voor DuckDB heeft een rijke set ruimtelijke functies.<\/P>Zet nu de dataframe om naar een feature class.  Je hebt andere conversieopties. Merk op dat de arcgis Python API geen idee heeft waar de dataframe vandaan komt - DuckDB doet het prima! - dus ik maak de dataframe ruimtelijk bruikbaar en schrijf dan naar een geodatabase feature class.  Geen gedoe met attribuutveld eigenschappen, ze werken gewoon (zie de SQL hierboven, het klassieke geval van ZIP-codes casten naar tekst kwam voorbij).<\/P> <\/P># Schrijf de 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)<\/code><\/pre> <\/P>Tenslotte, aankondiging!<\/P> <\/P>print("Feature class {} aangemaakt".format(location))<\/code><\/pre> <\/P>Dat is het, snelle, eenvoudige, slimme migratie van data met wat hulp van DuckDB!<\/P>De notebook zit in de blog download, laat ons weten hoe het gaat.<\/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);