Más Desde El Estanque De Patos - Usando DuckDB En ArcGIS Pro

bharold

En una publicación anterior me inicié usando DuckDB en un cuaderno alojado en ArcGIS Online.  Esa publicación destacó la lectura de archivos GeoParquet en un object store para mantener un servicio de entidades.  Ese es un flujo de trabajo poderoso pero es mucho más probable que tu trabajo diario implique manejar formatos de archivo comunes que consumen demasiado tiempo para ingerir, como CSV, Excel y JSON.  Esta publicación trata sobre facilitar tu vida mostrando cómo DuckDB habilita SQL para cualquier formato de archivo y también soporta conversión simple a formatos Esri.  Si tienes habilidades básicas en SQL y Python - es decir, puedes leer este ejemplo y la ayuda web - estarás bien.<\/P>

Aquí hay algo en acción en vivo, incidentes abiertos 311 de Bloomington Indiana migrados a una clase de entidad en segundos.<\/P>

Incidentes 311 de Bloomington<\/span><\/span><\/P> <\/P>El cuaderno (en la descarga del blog) lee un archivo CSV desde una URL pública y escribe una clase de entidad en la geodatabase predeterminada del proyecto.  Los cuadernos son un buen lugar para comenzar comparado con una herramienta de script porque puedes trabajar interactivamente en una celda mientras descubres tu SQL.  Una vez que todo funcione podrías copiar el código a una herramienta de script para automatización fácil y programada.<\/P>Pero primero necesitas instalar DuckDB!  La ruta habitual para agregar un paquete Python a tu entorno es clonar el entorno arcgispro-py3 y luego agregar un paquete en la interfaz del Administrador de Paquetes backstage en Pro.  Buscarías un paquete llamado python-duckdb.  Esto me dio error (sucede) así que hice una instalación manual.  Cloné mi entorno predeterminado a uno que nombré arcgispro-py3-quack e instalé DuckDB abriendo el Símbolo del sistema de Python desde el grupo de programas ArcGIS y ejecutando este comando ('username' será el tuyo):<\/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>Asegúrate de obtener la última distribución DuckDB, al momento de escribir es la 1.0.0.<\/P>Vamos a recorrer cada celda del cuaderno.  La primera es bastante simple, las importaciones y algunas llamadas a duckdb para configurar el entorno.<\/P> <\/P># Importaciones, entorno

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>La segunda celda crea un dataframe pandas leyendo los datos CSV mientras infiera automáticamente los tipos de datos, una gran ayuda.  Notarás que programo defensivamente para la situación en que el servidor al que accedo no soporta acceso HTTP range request como DuckDB requiere - descargo los datos - lo cual fue necesario en este caso.<\/P> <\/P># Leer datos en 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)
# Construye la consulta SQL que deseas para renombrar, convertir o procesar los datos.
# Este conjunto tiene una columna en WKT ya, si tus datos no tienen entonces necesitarás
# crear una usando las funciones ST_ en 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>Nótese el punto sobre usar o crear valores WKT para tu geometría.  La extensión espacial para DuckDB tiene un conjunto rico de funciones espaciales.<\/P>Ahora convierte el dataframe a una clase de entidad.  Tienes otras opciones de conversión. Nota que la API Python arcgis no sabe de dónde vino el dataframe - ¡DuckDB funcionará bien! - así que habilito espacialmente el dataframe y luego escribo a una clase de entidad en la geodatabase.  No hay necesidad de manipular propiedades de campos atributo, simplemente funcionan (ver el SQL arriba, surgió la clásica situación de convertir códigos ZIP a texto).<\/P> <\/P># Escribir la clase de entidad

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>Finalmente, anuncio!<\/P> <\/P>print("Created feature class {}".format(location))<\/code><\/pre> <\/P>Eso es todo, migración rápida, simple e inteligente de datos con algo de ayuda de DuckDB!<\/P>El cuaderno está en la descarga del blog, cuéntanos cómo te va.<\/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);