hongbo-miao/hongbomiao.com

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7b5a1c2-11e8-4fe1-be0e-44add5fdad92",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%configure -f\n",
    "{\n",
    "  \"conf\": {\n",
    "    \"spark.sql.extensions\": \"io.delta.sql.DeltaSparkSessionExtension,org.apache.sedona.sql.SedonaSqlExtensions,org.apache.sedona.viz.sql.SedonaVizExtensions\",\n",
    "    \"spark.sql.catalog.spark_catalog\": \"org.apache.spark.sql.delta.catalog.DeltaCatalog\"\n",
    "  }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1b3d83d-6b44-448c-99d4-4aad8a974269",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "select\n",
    "    timestamp(unix_timestamp(cast(_time / 1000000000.0 as timestamp))) as `Time`,\n",
    "    st_distance(\n",
    "        st_geomfromwkt('POINT(34.052235 -118.243683)'),\n",
    "        st_geomfromwkt(_coordinate)\n",
    "    ) as `Distance to Los Angeles, CA`,\n",
    "    _coordinate,\n",
    "    desc,\n",
    "    ownop,\n",
    "    r,\n",
    "    t,\n",
    "    icao,\n",
    "    noregdata,\n",
    "    dbflags_military,\n",
    "    dbflags_interesting,\n",
    "    dbflags_pia,\n",
    "    dbflags_ladd,\n",
    "    trace_on_ground,\n",
    "    trace_altitude_ft,\n",
    "    trace_feeder_id,\n",
    "    trace_geometric_altitude_ft,\n",
    "    trace_geometric_vertical_rate_fpm,\n",
    "    trace_ground_speed_kt,\n",
    "    trace_indicated_airspeed_kt,\n",
    "    trace_position_type,\n",
    "    trace_roll_angle_deg,\n",
    "    trace_flags_altitude_type,\n",
    "    trace_flags_new_leg_start,\n",
    "    trace_flags_position_stale,\n",
    "    trace_flags_vertical_rate_type,\n",
    "    trace_track_deg,\n",
    "    trace_vertical_rate_fpm,\n",
    "    trace_aircraft_alert,\n",
    "    trace_aircraft_alt_geom,\n",
    "    trace_aircraft_baro_rate,\n",
    "    trace_aircraft_category,\n",
    "    trace_aircraft_emergency,\n",
    "    trace_aircraft_flight,\n",
    "    trace_aircraft_geom_rate,\n",
    "    trace_aircraft_gva,\n",
    "    trace_aircraft_ias,\n",
    "    trace_aircraft_mach,\n",
    "    trace_aircraft_mag_heading,\n",
    "    trace_aircraft_nac_p,\n",
    "    trace_aircraft_nac_v,\n",
    "    trace_aircraft_nav_altitude_fms,\n",
    "    trace_aircraft_nav_altitude_mcp,\n",
    "    trace_aircraft_nav_heading,\n",
    "    trace_aircraft_nav_qnh,\n",
    "    trace_aircraft_nic,\n",
    "    trace_aircraft_nic_baro,\n",
    "    trace_aircraft_oat,\n",
    "    trace_aircraft_rc,\n",
    "    trace_aircraft_roll,\n",
    "    trace_aircraft_sda,\n",
    "    trace_aircraft_sil,\n",
    "    trace_aircraft_sil_type,\n",
    "    trace_aircraft_spi,\n",
    "    trace_aircraft_squawk,\n",
    "    trace_aircraft_tas,\n",
    "    trace_aircraft_tat,\n",
    "    trace_aircraft_track,\n",
    "    trace_aircraft_track_rate,\n",
    "    trace_aircraft_true_heading,\n",
    "    trace_aircraft_type,\n",
    "    trace_aircraft_version,\n",
    "    trace_aircraft_wd,\n",
    "    trace_aircraft_ws\n",
    "from adsb_db.adsb_2x_flight_trace_data\n",
    "where _date between '2023-08-01' and '2023-08-03'\n",
    "order by _time desc\n",
    "limit 100;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e69f8d06-3a68-4d8f-9d6a-9910b054bef9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "select *\n",
    "from delta.`s3a://hm-production-bucket/delta-tables/motor_data`\n",
    "limit 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5acb807f6bb4656e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "create temporary view hm_motor\n",
    "using org.apache.spark.sql.parquet\n",
    "options (\n",
    "    path \"s3a://hm-production-bucket/raw-parquet/motor.parquet\"\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1297202636a6ff69",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "select * from hm_motor;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PySpark",
   "language": "python",
   "name": "pysparkkernel"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "python",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "pyspark",
   "pygments_lexer": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}