Více z Duck Pond - Použití DuckDB v ArcGIS Pro

bharold

V předchozím příspěvku jsem si vyzkoušel použití DuckDB v hostovaném notebooku v ArcGIS Online.  Tento příspěvek zdůraznil čtení GeoParquet souborů v object store pro udržení feature service.  To je silný pracovní postup, ale je mnohem pravděpodobnější, že vaše každodenní práce zahrnuje manipulaci s běžnými formáty souborů, které zabírají příliš mnoho času na načtení - jako CSV, Excel a JSON.  Tento příspěvek je o tom, jak si usnadnit život tím, že ukáže, jak DuckDB umožňuje SQL pro jakýkoli formát souboru a také podporuje jednoduchou konverzi do Esri formátů.  Pokud máte základní znalosti SQL a Pythonu - tedy pokud dokážete přečíst tento příklad a webovou nápovědu - bude to pro vás v pořádku.<\/P>

Zde je živá ukázka, otevřené incidenty 311 v Bloomington Indiana migrované do feature class během několika sekund.<\/P>

Bloomington 311 Incidents<\/span><\/P> <\/P>Notebook (v blogovém downloadu) čte CSV soubor z veřejné URL a zapisuje feature class do výchozí geodatabáze projektu.  Notebooky jsou dobrým místem pro začátek ve srovnání se skriptovým nástrojem, protože můžete interaktivně pracovat v buňce, zatímco ladíte své SQL.  Jakmile vše funguje, můžete zkopírovat kód do skriptového nástroje pro snadnou automatizaci podle plánu.<\/P>Ale nejprve musíte nainstalovat DuckDB!  Obvyklá cesta k přidání Python balíčku do vašeho prostředí je klonovat prostředí arcgispro-py3 a poté přidat balíček v Package Manager UI backstage v Pro.  Hledali byste balíček s názvem python-duckdb.  To mi způsobilo chybu (stává se), takže jsem provedl manuální instalaci.  Naklonoval jsem své výchozí prostředí do nového pojmenovaného arcgispro-py3-quack a nainstaloval DuckDB otevřením Python Command Prompt z ArcGIS programové skupiny a spuštěním tohoto příkazu ('username' bude váš):<\/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>Ujistěte se, že máte nejnovější distribuci DuckDB, v době psaní je to 1.0.0.<\/P>Pojďme projít každou buňku notebooku.  První je docela jednoduchá, importy a několik volání duckdb pro nastavení prostředí.<\/P> <\/P># Importy, prostředí

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>Druhá buňka vytvoří pandas dataframe načtením CSV dat při automatickém odhadu datových typů - to je velká pomoc.  Všimnete si, že kóduji obezřetně pro situaci, kdy server, na který se připojuji, nepodporuje HTTP range request přístup tak, jak DuckDB požaduje - stáhnu data - což bylo v tomto případě nutné.<\/P> <\/P># Načíst data do 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)
# Sestavte SQL dotaz podle potřeby pro přejmenování, převod nebo jinou úpravu dat.
# Tento dataset již má sloupec ve WKT, pokud vaše data nemají,
# budete muset jeden vytvořit pomocí ST_ funkcí v 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>Poznámka k používání nebo vytváření WKT hodnot pro vaši geometrii.  Spatial extension pro DuckDB má bohatou sadu prostorových funkcí.<\/P>Nyní převedeme dataframe na feature class.  Máte i jiné možnosti konverze. Všimněte si, že arcgis Python API neví odkud dataframe pochází - DuckDB to zvládne! - takže dataframe prostorově povolím a pak zapíšu do geodatabázové feature class.  Žádná manipulace s vlastnostmi atributových polí není potřeba, prostě fungují (viz SQL výše, klasická situace převodu ZIP kódů na text se objevila).<\/P> <\/P># Zapsat 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>A nakonec oznámení!<\/P> <\/P>print("Vytvořena feature class {}".format(location))<\/code><\/pre> <\/P>Toto je vše – rychlá, jednoduchá a chytrá migrace dat s pomocí DuckDB!<\/P>Notebook je v blogovém downloadu, dejte nám vědět jak vám to šlo.<\/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);