Published

2025-05-14 10:46:47

1 Write parquet locally

Code
# packages
librarian::shelf(
  arrow, dplyr, geoarrow, here, mapview, sf, tibble,
  quiet = T)

# paths
nc_gp <- system.file("gpkg/nc.gpkg", package = "sf")
nc_pq <- here("data/geoarrow/nc.parquet")

# read geopackage
nc <- read_sf(nc_gp)

# show North Carolina counties
mapView(nc)
Code
# write parquet
if (!file.exists(nc_pq))
  nc |> 
    as_tibble() |> 
    write_parquet(nc_pq)

# compare file sizes
tibble(
  obj = c("nc_gp", "nc_pq")) |>
  mutate(
    mb = file.info(c(nc_gp, nc_pq))$size/1024^2)
# A tibble: 2 × 2
  obj       mb
  <chr>  <dbl>
1 nc_gp 0.119 
2 nc_pq 0.0367

2 Read parquet locally

Code
# filter and map
open_dataset(nc_pq) |> 
  filter(startsWith(NAME, "A")) |>
  select(NAME, geom) |> 
  st_as_sf() |> 
  mapView(layer.name = "A counties")

3 Setup S3 AWS Bucket

https://us-east-1.console.aws.amazon.com/s3/buckets/oceanmetrics.io-public

Bucket policy for public read access:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "PublicReadGetObject",
            "Effect": "Allow",
            "Principal": "*",
            "Action": [
                "s3:GetObject"
            ],
            "Resource": [
                "arn:aws:s3:::Bucket-Name/*"
            ]
        }
    ]
}

Policy for Cross-origin resource sharing (CORS):


[
    {
        "AllowedHeaders": [],
        "AllowedMethods": [
            "GET"
        ],
        "AllowedOrigins": [
            "*"
        ],
        "ExposeHeaders": []
    }
]

4 Push parquet to S3

Code
stopifnot(arrow_with_s3())

librarian::shelf(
  glue, janitor, readr, tibble,
  quiet = T)
options(readr.show_col_types = F)

d_aws <- read_csv(
  '/Users/bbest/Library/CloudStorage/GoogleDrive-ben@ecoquants.com/My Drive/private/ben_ben@ecoquants.com_console.aws.amazon.com_accessKeys.csv') |> 
  clean_names()
Sys.setenv(
  "AWS_ACCESS_KEY_ID"     = d_aws$access_key_id,
  "AWS_SECRET_ACCESS_KEY" = d_aws$secret_access_key,
  "AWS_DEFAULT_REGION"    = "us-east-1")
bucket <- "oceanmetrics.io-public"

# load/reload aws.s3 to read credentials
unloadNamespace("aws.s3")
librarian::shelf(
  aws.s3,
  quiet = T)
# bucketlist()

stopifnot(bucket_exists(bucket))
stopifnot(exists("nc_pq"))

o      <- basename(nc_pq)
o_file <- nc_pq

if (!object_exists(o, bucket))
  put_object(o_file, o, bucket)

# put_acl(o, bucket, acl = "public-read")
# "The bucket does not allow ACLs"

# check
o_s3 <- glue("s3://{bucket}/{o}")
object_exists(o_s3)
[1] TRUE

5 Read parquet from S3

Code
# standalone R chunk

librarian::shelf(
  arrow, dplyr, geoarrow, glue, mapview, sf,
  quiet = T)

bucket <- "oceanmetrics.io-public"
obj    <- "nc.parquet"

open_dataset(glue("s3://{bucket}/{obj}")) |>
  filter(startsWith(NAME, "A")) |>
  select(NAME, geom) |>
  st_as_sf() |>
  mapView(layer.name = "A counties")

6 Read parquet with ObservableJS

6.1 Read locally

Code
ncdb = DuckDBClient.of({
  nc: FileAttachment("data/geoarrow/nc.parquet")
});

nc_a_local = ncdb.query("SELECT * EXCLUDE (geom) FROM nc WHERE NAME LIKE 'A%';");
Inputs.table(nc_a_local)

6.2 Read remote S3

Code
db = {
  var c = await DuckDBClient.of();
  /*
  // Uncomment to install the spatial extension, but this will not work 
  // in Observable with Quarto
  //   Error: Invalid Input Error: Attempting to fetch from an unsuccessful 
  //          query result
  //   Error: IO Error: Can't find the home directory at '/home/web_user' 
  //          Specify a home directory using the SET home_directory='/path/to/dir' option.
  await c.query(`SET home_directory = '/tmp';`);
  await c.query(`INSTALL spatial;`);
  await c.query(`LOAD spatial;`);
  */
  return c;
}
Code
db_settings = db.query(`SELECT * FROM duckdb_settings();`)
Inputs.table(db_settings)
Code
pq = 'https://s3.us-east-1.amazonaws.com/oceanmetrics.io-public/nc.parquet';

nc_flds = db.query(`DESCRIBE SELECT * FROM '` + pq + `';`)
Inputs.table(nc_flds)
Code
nc_a_remote = db.query(`SELECT * EXCLUDE (geom) FROM '` + pq + `' WHERE NAME LIKE 'A%';`);
Inputs.table(nc_a_remote)

Spatial extension db.query("INSTALL spatial;LOAD spatial;") not working in Observable {ojs} chunk in Quarto:

More resources:

7 Notes: Google Cloud Storage not working

Code
# set env BEFORE loading googleCloudStorageR
gcs_json   = "/Users/bbest/Library/CloudStorage/GoogleDrive-ben@ecoquants.com/My Drive/private/offhab-google-service-account_09e7228ac965.json"
gcs_bucket = "oceanmetrics"
Sys.setenv(
  "GCS_DEFAULT_BUCKET" = gcs_bucket,
  "GCS_AUTH_FILE"      = gcs_json)

# packages
librarian::shelf(
  googleCloudStorageR, targets,
  quiet = T)

gcs_get_bucket(gcs_bucket)

o_file <- nc_pq
o      <- basename(nc_pq)

# upload
gcs_upload(
  file            = o_file,
  name            = o,
  object_metadata = gcs_metadata_object(
    o,
    metadata = list(
      state = "NC",
      notes = "test metadata")))

# make publicly visible
gcs_update_object_acl(
  o, entity_type = "allUsers", role = "READER")

# get URL
(url <- gcs_download_url(o))
Code
# Sys.setenv(ARROW_R_DEV=TRUE); install.packages("arrow")
librarian::shelf("arrow",
  quiet = T)

# https://github.com/apache/arrow/issues/44859#issuecomment-2502344866
# brew install openssl@3
Sys.setenv("ARROW_R_DEV"=TRUE, "NOT_CRAN" = "true")
install.packages("arrow", type = "source")
install.packages("arrow")

arrow_with_gcs()
arrow_info()

nc_pq <- "https://storage.cloud.google.com/oceanmetrics/nc.parquet"

open_dataset(nc_pq) |> 
  filter(startsWith(NAME, "A")) |>
  select(NAME, geom) |> 
  st_as_sf() |> 
  mapView(layer.name = "A counties")