Amplitude SQL: Table schema and field shortcuts

Special field shortcuts

Because Amplitude SQL is built directly into the Amplitude chart experience, you can leverage much of the same familiar Amplitude user interface, such as the datepicker chart saving experience. You can do this by using Amplitude SQL's special fields for powerful shortcuts:

• $date: When using this shortcut, the time range chosen by the datepicker is automatically applied and will update the query over time. It refers to the event time on the event and will respect the timezone the project has been set to. Otherwise, Amplitude SQL will return data in UTC. This must be used in conjunction with $events.
• $events: The shorthand used to refer to the table in your current project. When using this table, merged users are automatically handled. Note: This must be used in conjunction with $date.
• $amplitude_id: The original Amplitude ID for the user. Use this field to automatically handle merged users.

Table schema

Amplitude SQL uses a one-table schema. The table, $events, handles the merged user mappings automatically. But, the merged users table will also be available to view. This will allow you to quickly see the number of users that have been merged into one.

The following tables show the schema of $events and merged users.

$events table

• $amplitude_id
  • Data type: NUMBER(38,0)
  • Description: The original Amplitude ID for the user. Use this field to automatically handle merged users.
• adid
  • Data type: VARCHAR(16777216)
  • Description: (Android) Google Play Services advertising ID (AdID). This usually is wiped after ingestion and therefore will be blank.
• amplitude_attribution_ids
  • Description: Anonymized hash of the advertising IDs that we store for internal purposes; not useful for the customer by any means. But this will appear if advertising IDs were sent which proves that adid/idfv existed even though currently wiped.
• amplitude_event_type
  • Data type: VARCHAR(16777216)
  • Description: Amplitude specific identifiers based on events Amplitude generates. This is a legacy field so event_type should suffice for all queries.
• amplitude_id
  • Data type: NUMBER(38,0)
  • Description: An internal ID used to count unique users.
• app
  • Data type: NUMBER(38,0)
  • Description: Project ID found in your project's Settings page.
• city
  • Data type: VARCHAR
  • Description: City.
• client_event_time
  • Data type: TIMESTAMP
  • Description: Local timestamp (UTC) of when the device logged the event.
• client_upload_time
  • Data type: TIMESTAMP
  • Description: The local timestamp (UTC) of when the device uploaded the event.
• country
  • Data type: VARCHAR
  • Description: Country.
• data
  • Data type: VARIANT
  • Description: Dictionary where certain fields such as first_event and merged_amplitude_id are stored.
• device_brand
  • Data type: VARCHAR(16777216)
  • Description: Device brand.
• device_carrier
  • Data type: VARCHAR(16777216)
  • Description: Device carrier.
• device_family
  • Data type: VARCHAR(16777216)
  • Description: Device family.
• device_id
  • Data type: VARCHAR(16777216)
  • Description: The device specific identifier.
• device_manufacturer
  • Data type: VARCHAR(16777216)
  • Description: Device manufacturer.
• device_model
  • Data type: VARCHAR(16777216)
  • Description: The device model.
• device_type
  • Data type: VARCHAR(16777216)
  • Description: Device type.
• dma
  • Data type: VARCHAR(16777216)
  • Description: Designated marketing area (DMA).
• event_id
  • Data type: NUMBER(38,0)
  • Description: A counter that distinguishes events.
• event_time
  • Data type: TIMESTAMP
  • Description:
    • Amplitude timestamp (UTC) which is the client_event_time adjusted by the difference between server_received_time and client_upload_time, specifically:
    • event_time = client_event_time + (server_received_time - client_upload_time)
    • We use this timestamp to organize events on Amplitude charts.
    • If the difference between server_received_time and client_upload_time is less than 60 seconds, the event_time will not be adjusted and will equal the client_event_time.
• event_type
  • Data type: VARCHAR(16777216)
  • Description: The assigned type of event.
• followed_an_identify
  • Data type: BOOLEAN
  • Description: True if there was an identify event between this current SDK event and the last SDK event seen.
• groups
  • Data type: VARIANT
  • Description: Group types. See theAccounts documentation for more information.
• idfa
  • Data type: VARCHAR(16777216)
  • Description: (iOS) Identifier for Advertiser. This usually is wiped after ingestion and therefore will be blank.
• ip_address
  • Data type: VARCHAR(16777216)
  • Description: IP address.
• location_lat
  • Data type: FLOAT
  • Description: Latitude.
• location_lng
  • Data type: FLOAT
  • Description: Longitude.
• os_name
  • Data type: VARCHAR(16777216)
  • Description: OS name.
• os_version
  • Data type: VARCHAR(16777216)
  • Description: OS version.
• paying
  • Data type: VARCHAR
  • Description: True if the user has ever logged any revenue, otherwise '(none)'. The property value can be modified via the Identify API.
• region
  • Data type: VARCHAR
  • Description: Region.
• server_upload_time
  • Data type: TIMESTAMP
  • Description: Amplitude timestamp (UTC) of when our servers received the event.
• session_id
  • Data type: NUMBER(38,0)
  • Description: The session start time in milliseconds since epoch.
• start_version
  • Data type: VARCHAR
  • Description: App version the user was first tracked on.
• user_id
  • Data type: VARCHAR(16777216)
  • Description: A readable ID specified by you.
• uuid
  • Data type: VARCHAR(16777216)
  • Description: A unique identifier per row (event sent).
• version_name
  • Data type: VARCHAR(16777216)
  • Description: The app version.

Merged users table

See this article to learn more about how Amplitude tracks unique users.