[INTERNAL] DRAFT >>> BigQuery - Important Tables for Internal Use

BQ Tables to aid customized query development for client data extracts and samples

All folders and tables listed in the table below are located in the ais-data-analysis project folder in BigQuery.

In BigQuery, hierarchy of the path Project > Data Set > Table. There can be multiple data sets before the final table. The Table Path is given using the following format in the tables below:


Descriptions for each table and important data tables are included below. Big Query SQL Templates FAQ can be found here: https://faq.spire.com/sales-engineering-sql-templates.

    >>> 🚢 MARITIME 🌊  <<< 

    🚢 customer_analytics Dataset

    Table Name Description Table Path Note(s)
    navtor_history20182019_positions  Dynamic AIS history data ais-data-analysis.customer_analytics.navtor_history20182019_positions   
    navtor_history20182019_svd Dynamic AIS history data ais-data-analysis.customer_analytics.navtor_history20182019_svd  
    DAIS_history_coverage_20200520 Coverage data for the Dynamic AIS History 2018-08 to 2019-12 ais-data-analysis.customer_analytics.DAIS_history_coverage_20200520  


    🚢 cx_mechanism Dataset

    Metrics Reports + Vessels API Data

    Table Name Description Table Path Note(s)
    all_ais_3hr_downsample Copy of ais_messages__production.downsampled table with only relevant AIS columns. Downsampled to 3 hrs instead of 5 min.  ais-data-analysis.cx_mechanism.all_ais_3hr_downsample  


    Used in CX Vessels Report
    daily_latency_metrics Gets daily satellite latency.  ais-data-analysis.cx_mechanism.data_latency_metrics (Target = 40 minutes as of 2020)
    fleetmon_2014_2016 Historical AIS data that pre-dates Spire Maritime. Used in Historical AIS SQL query template. ais-data-analysis.cx_mechanism.fleetmon_2014_2016  
    imo_mmsi_matching Used to match given IMOs to MMSIs. For every day, looks at previous 90 days and gets a pair of IMOs and MMSIs. ais-data-analysis.cx_mechanism.imo_mmsi_matching  
    maritime_metrics Given a day and a collection type, returns number of AIS messages, number of unique mmsi, and median latency.  ais-data-analysis.cx_mechanism.maritime_metrics Used for internal dashboard: https://datastudio.google.com/u/0/reporting/1UYosI2fqhbVEw7n9OdCLHzOCtu_0SlcU/page/sQjFB
    median_latency_day_collection_type Median latency for different combinations of collection types. ais-data-analysis.cx_mechanism.median_latency_day_collection_type  
    messages_logs_stats_daily Per hour, per user, the stats from that user. ais-data-analysis.cx_mechanism.messages_logs_stats_daily Used for internal dashboard: https://datastudio.google.com/u/0/reporting/7484f3c8-5491-4f18-ab77-ec7e2d495a90/page/C0ykB
    mmsi_draught_minmax Gets the min, max, median, and standard draft for a specified mmsi over a specified extract date.  ais-data-analysis.cx_mechanism.mmsi_draught_minmax Used to estimate cargo loads.
    mmsi_shiptype_matching Matches MMSI to a shiptype. ais-data-analysis.cx_mechanism.mmsi_shiptype_matching Used in queries where you want to specify a certain ship type from AIS (not EVD).
    mmsi_steaming_speed Monthly median and average speed for an mmsi recognized as when the vessel is underway. mmsi_streaming_speed Use to get average or median speed the vessel is traveling at.

    Hourly capture of Vessels API. Includes EVD data.





    Hourly capture of Vessels API from non DAIS feed. Includes EVD data 




    🚢 customer_sample_temporary Dataset

    Use this dataset to store any customer data sample tables that do not need to be kept longer than 40 days.

    No specific tables in the dataset are required for SQL template creation.

    🚢 HTZ_Analysis Dataset

    Port Events Data

    Table Name File Path Description Note(s)
    vessels_in_ports_events_v1   ais-data-analysis.HTZ_Analysis.vessels_in_ports_events_v1  


    🚢 ais_messages__production Dataset

    Messages API data

    Table Name Description File Path Note(s)
    downsampled Default Messages API table.  To downsample - For terrestrial data, we only take one message per MMSI per 5 minutes. Contains more metadata about satellite related information.    
    messages_request_event Use of Messages API. Raw log of every Messages API request.    
    full Non-Downsampled table. Includes every message received.    
    messages_data_out_log     ???
    navtor_history20182019_svd Historical Dynamic Data from Dynamic data supplier.   ** WHY IS THIS SAVED HERE?? DO WE NEED IT??

    🚢 evd_brl Dataset

    EVD Data from BRL

    Table Name Description File Path Note(s)
    EVD_BRL_ALL All the EVD from provider BRL  ais-data-analysis.evd_brl. EVD_BRL_ALL This table is not updated on a regular basis
    EVD_BRL_DATA Bulk Carrier EVD from provider BRL  ais-data-analysis.evd_brl. EVD_BRL_DATA This table is updated monthly
    EVD_BRL_ContainerShips Containership EVD from provider BRL  ais-data-analysis.evd_brl. EVD_BRL_ContainerShips This table is updated weekly
    Shipamax_BulkCarrier_EVD All the EVD that Shipamax needs (combined from BRL and Gibsons) for Bulk Carriers ais-data-analysis.evd_brl.Shipamax_BulkCarrier_EVD This table is updated monthly
    Ecumene_ContainerShip_EVD All the EVD (combined from BRL and Gibsons) for Containerships ais-data-analysis.evd_brl.Ecumene_ContainerShip_EVD This table is updated weekly

    🚢 duplicate_vessels Dataset

    list of duplicate vessels

    Table Name Description File Path Note(s)
    DUPLICATE_VESSELS_LIST This table contains list of duplicate vessels based on MMSI and IMO numbers extracted from previous day of data ais-data-analysis.duplicate_vessels.DUPLICATE_VESSELS_LIST  This table is updated on a daily basis 

    🚢 decoded_nmea Dataset

    decoded information from static nmea messages(currently all tables  in this dataset are important as decoding work is still ongoing, I will update this section once the decoding work is finished)

    Table Name Description File Path Note(s)



    >>> ✈️ AVIATION ✈️ <<<



    Table Name File Path Description Note(s)
    adsb.*   The whole dataset is used by aviation engineering for storage/logging/testing purposes.  
    airsafe_metrics.*   The dataset is used to store SE monitoring/tools data.  
    apigee_daily_metrics.*   The dataset is used by the respective Cloud Functions for each division (Maritime, Weather, Aviation) and is storing the Apigee applications state on a daily basis.  



    >>> ⛅ WEATHER ⛅ <<<



    Table Name File Path Description Note(s)