--- title: Access CX-Data directly description: This guide explains how to query your S3 Coralogix archive bucket (cx-data) using a third-party framework with the standard Apache Parquet reader provided by the framework and the required schema. url: https://coralogix.com/docs/user-guides/data-query/archive-query/access-cx-data-directly/ product: dataengine feature: DataPrime Queries feature_id: dataengine-dataprime-queries feature_identifier: dataengine/dataengine-dataprime-queries last_updated: '2026-11-12' --- # Access CX-Data directly This guide explains how to query your S3 Coralogix archive bucket (`cx-data`) using a third-party framework with the standard [Apache Parquet](https://parquet.apache.org/) reader provided by the relevant framework and required schema. ## Folder structure `cx-data` is stored in standard hive-like partitions, with the following partition fields: * `team_id=`: [Coralogix Team ID](https://coralogix.com/docs/user-guides/account-management/account-settings/team-id.md) * `dt=YYYY-MM-DD`: Date of the data in UTC * `hr=HH`: Hour of the data in UTC These fields can be defined as virtual columns inside the framework, serving as filters in a query. **Note**: * Both `dt` and `hr` are based on the event timestamp. * The `team_id=` partition allows reusing the same bucket and prefix to write data from multiple Coralogix teams and query them all in one query. ## Fields Each Apache Parquet file has three fields with data as JSON-formatted strings: * `src_obj__event_metadata`: JSON object containing metadata related to the event * `src_obj__event_labels`: JSON object containing the labels of the event (such as the Coralogix [applicationName and subsystemName](https://coralogix.com/docs/user-guides/account-management/account-settings/application-and-subsystem-names.md)) * `src_obj__user_data`: JSON object containing actual event data ### Examples Below is an example of each of the 3 payload fields. `src_obj__event_metadata`: ``` { "timestamp": "2022-03-28T08:50:57.946", "severity": "Debug", "priorityclass": "low", "logid": "some-uuid" } ``` `src_obj__event_labels`: ``` { "applicationname": "some-app", "subsystemname": "some-subsystem", "category": "some-category", "classname": "some-class", "methodname": "some-method", "computername": "some-computer", "threadid": "some-thread-id", "ipaddress": "some-ip-address" } ``` `src_obj__user_data`: ``` { "_container_id": "0f099482cf3b507462020e9052516554b65865fb761af8e076735312772352bf", "host": "ip-10-1-11-144", "short_message": "10.1.11.144 - - [28/Mar/2022:08:50:57 +0000] \\"GET /check HTTP/1.1\\" 200 16559 \\"-\\" \\"Consul Health Check\\" \\"-\\"" } ``` ## Reading `cx-data` files using a standard framework ### Pandas Loading `cx-data` files in Pandas can be done using the `read_parquet` method: ``` import pandas as pd # Notice that only the three payload columns are passed eventually to read_parquet() cx_columns = [ 'src_obj__event_metadata', 'src_obj__event_labels', 'src_obj__user_data' ] df = pd.read_parquet('s3://.../myfile.parquet',columns = cx_columns) # The dataframe `df` contains all the data needed for further processing. ``` Here is the output of `df.info()` showing the expected schema of the DataFrame: ``` print(df.info()) ### Output RangeIndex: 161050 entries, 0 to 161049 Data columns (total 3 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 src_obj__event_metadata 161050 non-null object 1 src_obj__event_labels 161050 non-null object 2 src_obj__user_data 161050 non-null object dtypes: object(3) ``` ### Athena To use `cx-data` directly in Athena, you’ll need to create an `EXTERNAL` table as follows: ``` CREATE EXTERNAL TABLE IF NOT EXISTS "my_table" ( `src_obj__event_labels` STRING, `src_obj__event_metadata` STRING, `src_obj__user_data` STRING ) PARTITIONED BY (`team_id` string, `dt` string, `hr` string) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3:///cx/parquet/v1/' ``` After creating the external table, table partitions should be added to limit the scope of queries, reducing costs and improving performance. Table partitions can be added manually: ``` ALTER TABLE "my_table" ADD PARTITION (team_id='23333', dt='2022-05-30', hr='01'); # Additional ALTER TABLE ... ADD PARTITION statements according to which dates and hours are needed ``` Table partitions can also be added automatically: ``` MSCK REPAIR TABLE "my_table" ``` **Note**! This command scans all of the files in the table location, which may be a time-consuming task when the amount of partitions detected is large. It may be easier to add the relevant new partitions manually. One option to expedite automatic partitioning is to limit the scope of the data by specifying a specific `team_id` and `dt` in the table definition `LOCATION` (e.g. modify the `CREATE EXTERNAL TABLE` command to include the `team_id=/dt=/` subpath). This restricts the partition auto-detection to the relevant dates. #### Permissions for adding partitions `ALTER TABLE ... ADD PARTITION` and `MSCK REPAIR TABLE` write partitions to the AWS Glue Data Catalog. Without write access to the catalog, `MSCK REPAIR TABLE` can list the partitions it finds on S3 as "Partitions not in metastore" and still fail to add them. Grant the IAM principal that runs the Athena queries, such as the user or the workgroup role, these actions on the Glue database and table: | Action | Purpose | | ----------------------------------------- | --------------------------------------- | | `glue:BatchCreatePartition` | Add the partitions to the table. | | `glue:GetDatabase` | Read the database. | | `glue:GetTable` | Read the table definition. | | `glue:GetPartition`, `glue:GetPartitions` | Read the partitions already registered. | Note If the Glue database or table is registered in AWS Lake Formation, Glue calls fail even with these IAM permissions. Grant the principal Lake Formation permissions on the database and table, including access to the underlying data, or deregister the resource from Lake Formation. Partitions you add this way cover only the data that exists when you add them. Each new day and hour needs another `MSCK REPAIR TABLE` run or `ADD PARTITION` statement. For more information, see [MSCK REPAIR TABLE troubleshooting](https://docs.aws.amazon.com/athena/latest/ug/msck-repair-table.html#msck-repair-table-troubleshooting) in the AWS documentation. To avoid adding partitions and the Glue write permissions altogether, use [partition projection](#query-with-partition-projection). After adding the relevant partitions, a query can be executed on the table. Accessing a specific field inside one of the payload fields should be done using the Athena function `json_extract_scalar`, as in the following example: ``` SELECT json_extract_scalar(src_obj__event_metadata,'$.severity') severity, json_extract_scalar(src_obj__event_labels,'$.applicationName') app_name, json_extract_scalar(src_obj__user_data,'$.my_obj.my_message_field') message_field FROM "my_table" ``` #### Query with partition projection With [Athena partition projection](https://docs.aws.amazon.com/athena/latest/ug/partition-projection.html), Athena works out the partition values at query time from the table properties, so you never add partitions to the Glue Data Catalog. You don't need `MSCK REPAIR TABLE`, `ALTER TABLE ... ADD PARTITION`, or Glue write permissions, and new days and hours are queryable as soon as they arrive. Create the table with projection enabled: ``` CREATE EXTERNAL TABLE IF NOT EXISTS "my_table" ( `src_obj__event_labels` STRING, `src_obj__event_metadata` STRING, `src_obj__user_data` STRING ) PARTITIONED BY (`team_id` string, `dt` string, `hr` string) STORED AS PARQUET LOCATION 's3:///cx/parquet/v1/' TBLPROPERTIES ( 'projection.enabled'='true', 'projection.team_id.type'='enum', 'projection.team_id.values'='', 'projection.dt.type'='date', 'projection.dt.format'='yyyy-MM-dd', 'projection.dt.range'='2026-01-01,NOW', 'projection.dt.interval'='1', 'projection.dt.interval.unit'='DAYS', 'projection.hr.type'='integer', 'projection.hr.range'='0,23', 'projection.hr.digits'='2', 'storage.location.template'='s3:///cx/parquet/v1/team_id=${team_id}/dt=${dt}/hr=${hr}' ) ``` * `projection.team_id.values`: your [Team ID](https://coralogix.com/docs/user-guides/account-management/account-settings/team-id.md). If the bucket holds data from several teams, list them comma-separated, such as `111111,222222`. * `projection.dt.range`: the first and last dates Athena considers. Set the start date to limit how far back queries can reach. * `storage.location.template`: Athena builds each partition's S3 path from this template and ignores `LOCATION`. Keep `LOCATION` at the base prefix. Narrowing it doesn't limit what Athena reads. Always filter on `team_id`, `dt`, and `hr`. Athena prunes only on projected columns, so a query without these filters lists every partition in the range and scans the full prefix. Use the same formats as the partitions: `yyyy-MM-dd` dates and zero-padded hours. ``` SELECT json_extract_scalar(src_obj__event_metadata,'$.severity') severity, json_extract_scalar(src_obj__user_data,'$.my_obj.my_message_field') message_field FROM "my_table" WHERE team_id = '' AND dt = '2026-10-22' AND hr = '04' AND json_extract_scalar(src_obj__event_labels,'$.applicationName') = 'my_application' ``` To cap the cost of a query that misses these filters, set a data-scanned limit on the Athena workgroup.