Mais Do Duck Pond - Usando DuckDB no ArcGIS Pro

bharold

Em uma postagem anterior eu comecei a usar DuckDB em um notebook hospedado no ArcGIS Online.  Essa postagem destacou a leitura de arquivos GeoParquet em um object store para manter um serviço de feição.  Esse é um fluxo de trabalho poderoso, mas é muito mais provável que seu trabalho diário envolva manipular formatos de arquivo comuns que consomem muito tempo para ingerir - como CSV, Excel e JSON.  Esta postagem é sobre facilitar sua vida mostrando como DuckDB habilita SQL para qualquer formato de arquivo e também suporta conversão simples para formatos Esri.  Se você tem habilidades básicas em SQL e Python - ou seja, consegue ler este exemplo e a ajuda na web - você vai se sair bem.<\/P>

Aqui está uma ação ao vivo, incidentes abertos 311 de Bloomington Indiana migrados para uma classe de feição em segundos.<\/P>

Incidentes 311 de Bloomington<\/span><\/P> <\/P>O notebook (no download do blog) lê um arquivo CSV em uma URL pública e grava uma classe de feição no geodatabase padrão do projeto.  Notebooks são um bom lugar para começar comparado a uma ferramenta script porque você pode trabalhar interativamente em uma célula enquanto descobre seu SQL.  Quando tudo estiver funcionando, você pode copiar o código para uma ferramenta script para automação fácil em um cronograma.<\/P>Mas primeiro você precisa instalar DuckDB!  A rota usual para adicionar um pacote Python ao seu ambiente é clonar o ambiente arcgispro-py3 e depois adicionar um pacote na interface do Gerenciador de Pacotes nos bastidores do Pro.  Você procuraria por um pacote chamado python-duckdb.  Isso deu erro para mim (acontece), então fiz uma instalação manual.  Eu clonei meu ambiente padrão para um que nomeei arcgispro-py3-quack e instalei DuckDB abrindo o Prompt de Comando Python do grupo de programas ArcGIS e executando este comando ('username' será o seu):<\/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>Certifique-se de obter a distribuição DuckDB mais recente, no momento da escrita é a 1.0.0.<\/P>Vamos passar por cada célula do notebook.  A primeira é bem simples, as importações e algumas chamadas ao duckdb para configurar o ambiente.<\/P> <\/P># Importações, ambiente

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>A segunda célula cria um dataframe pandas lendo os dados CSV enquanto infere automaticamente os tipos de dados - uma grande ajuda.  Você notará que eu programo defensivamente para a situação em que o servidor que estou acessando não suporta acesso HTTP range request como o DuckDB deseja - eu baixo os dados - o que neste caso foi necessário.<\/P> <\/P># Ler dados no 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)
# Construa a consulta que deseja em SQL para renomear, converter ou processar os dados.
# Este conjunto de dados já tem uma coluna em WKT, se seus dados não tiverem,
# você precisará criar uma usando as funções ST_ no 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>Note o ponto sobre usar ou criar valores WKT para sua geometria.  A extensão espacial para DuckDB possui um conjunto rico de funções espaciais.<\/P>Agora converta o dataframe para uma classe de feição.  Você tem outras opções de conversão. Note que a API Python do arcgis não sabe de onde veio o dataframe - DuckDB vai funcionar bem! - então eu habilito espacialmente o dataframe e depois escrevo para uma classe de feição no geodatabase.  Sem manipulação das propriedades dos campos de atributo, elas simplesmente funcionam (veja o SQL acima, a situação clássica de converter códigos ZIP para texto apareceu).<\/P> <\/P># Gravar a classe de feição

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>Por fim, anúncio!<\/P> <\/P>print("Classe de feição criada {}".format(location))<\/code><\/pre> <\/P>É isso aí, migração rápida, simples e inteligente dos dados com alguma ajuda do DuckDB!<\/P>O notebook está no download do blog, conte-nos como foi sua experiência.<\/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);