Plus Du Duck Pond - Utilisation de DuckDB dans ArcGIS Pro

bharold

Dans un article précédent, j'ai commencé à utiliser DuckDB dans un notebook hébergé sur ArcGIS Online.  Cet article mettait en avant la lecture de fichiers GeoParquet dans un object store pour maintenir un service de fonctionnalités.  C'est un flux de travail puissant mais il est beaucoup plus probable que votre travail quotidien implique de manipuler des formats de fichiers courants qui prennent beaucoup trop de temps à ingérer - comme CSV, Excel et JSON.  Cet article vise à vous faciliter la vie en montrant comment DuckDB permet le SQL pour n'importe quel format de fichier et prend également en charge la conversion simple vers les formats Esri.  Si vous avez des compétences de base en SQL et Python - c'est-à-dire que vous pouvez lire cet exemple et l'aide web - vous vous en sortirez très bien.<\/P>

Voici une démonstration en direct, les incidents Open 311 de Bloomington Indiana migrés vers une classe d'entités en quelques secondes.<\/P>

Incidents 311 de Bloomington<\/span><\/span><\/P> <\/P>Le notebook (dans le téléchargement du blog) lit un fichier CSV à une URL publique et écrit une classe d'entités dans la géodatabase par défaut du projet.  Les notebooks sont un bon point de départ comparé à un outil script car vous pouvez travailler dans une cellule de manière interactive pendant que vous élaborez votre SQL.  Une fois que tout fonctionne, vous pourriez copier le code dans un outil script pour une automatisation facile selon un planning.<\/P>Mais d'abord, vous devez installer DuckDB !  La méthode habituelle pour ajouter un package Python à votre environnement est de cloner l'environnement arcgispro-py3 puis d'ajouter un package via l'interface du gestionnaire de packages backstage dans Pro.  Vous chercheriez un package nommé python-duckdb.  Cela a généré une erreur pour moi (ça arrive) donc j'ai fait une installation manuelle.  J'ai cloné mon environnement par défaut vers un que j'ai nommé arcgispro-py3-quack et installé DuckDB en ouvrant l'invite de commande Python depuis le groupe de programmes ArcGIS puis en exécutant cette commande ('username' sera le vôtre) :<\/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>Assurez-vous d'obtenir la dernière distribution DuckDB, au moment où j'écris c'est la 1.0.0.<\/P>Passons en revue chaque cellule du notebook.  La première est assez simple, les imports et quelques appels à duckdb pour configurer l'environnement.<\/P> <\/P># Imports, environnement

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 deuxième cellule crée un dataframe pandas en lisant les données CSV tout en inférant automatiquement les types de données - une aide précieuse.  Vous remarquerez que je code prudemment pour le cas où le serveur que je consulte ne supporte pas l'accès HTTP range request comme DuckDB le souhaite - je télécharge les données - ce qui était nécessaire ici.<\/P> <\/P># Lire les données dans 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)
# Construisez la requête SQL souhaitée pour renommer, caster ou autrement traiter les données.
# Ce jeu de données a déjà une colonne en WKT, si vos données n'en ont pas alors vous devrez
# en créer une avec les fonctions ST_ dans 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>Notez l'importance d'utiliser ou de créer des valeurs WKT pour votre géométrie.  L'extension spatiale pour DuckDB offre un ensemble riche de fonctions spatiales.<\/P>Convertissez maintenant le dataframe en classe d'entités.  Vous avez d'autres options de conversion. Notez que l'API Python arcgis ne sait pas d'où vient le dataframe - DuckDB fera très bien l'affaire ! - donc je rends le dataframe spatialement actif puis j'écris dans une classe d'entités géodatabase.  Pas besoin de manipuler les propriétés des champs attributaires, elles fonctionnent simplement (voir le SQL ci-dessus, la situation classique du cast des codes ZIP en texte est apparue).<\/P> <\/P># Écrire la classe d'entités

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>Enfin, annonce !<\/P> <\/P>print("Classe d'entités créée {}".format(location))<\/code><\/pre> <\/P>C'est tout, migration rapide, simple et intelligente des données avec un peu d'aide de DuckDB !<\/P>Le notebook est dans le téléchargement du blog, dites-nous comment ça se passe.<\/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);