Querying company data on Amazon S3 with Athena

This guide queries Fokals company data where you keep it: in files in your own Amazon S3 bucket, read by Amazon Athena through external tables. It covers the folder layout, the choice between CSV and JSON Lines, a table definition for JSON Lines, day partitions with partition projection, and a conversion to Parquet that you run with CTAS. Fokals is delivered direct, by REST API and as bulk files in CSV, JSON Lines or JSON, which you copy into a bucket you control with the AWS tooling you already use. The examples are illustrative, for Acme Robotics.

Land the files in your own bucket

Athena queries files that already sit in S3 and loads nothing. A job of yours downloads each bulk export, or pages the API from the last cursor you stored, and copies the files to S3. The delivery page describes both routes. Use one prefix for each table, Hiring Activity here, and one folder for each period, named after the period in the Fokals manifest and in the dt= form that Athena reads as a partition: s3://acme-robotics-data/fokals/raw/company_hiring_daily/dt=2026-10-03/. Converted files go under a separate fokals/parquet/ prefix.

Athena reads every file in the folder that a table points to, and in every folder beneath a partitioned table. The AWS page on table locations says to keep anything you do not want read out of those folders. For Fokals that means the manifest, which names the sources, the period, the label versions and the licence, belongs under fokals/manifests/, and the CSV and JSON Lines forms of one table must never share a folder. Daily and weekly rows are written once and never changed, so a folder for a closed day is complete when it is written. Do not copy two deliveries of one period into the same folder, or Athena reads both.

Pick the format for each table

Athena's CSV reader, the Open CSV SerDe, has three behaviours that matter for company data. Among the types other than string it recognises only boolean, int, bigint and double, and it leaves an empty value in a numeric column as a string. It reads dates and timestamps only as Unix numbers, while Fokals times are ISO 8601 text. And it does not support line breaks inside a field, which any free-text column, such as the excerpt column of Company News, can contain. The Athena page on the CSV SerDe lists all three. If you take CSV, declare every column as string and cast in a view.

JSON Lines does not have these behaviours. The JSON SerDes expect one JSON record on each line, nested objects become struct or map columns, and a line break inside a string is escaped in JSON, so it cannot split a record. The Athena page on the OpenX SerDe warns that its results can vary and names the Hive JSON SerDe as the workaround, so the example below uses the Hive one. The comparison of CSV, JSON Lines and Parquet sets out the wider trade-offs.

An external table over JSON Lines

Two points in the definition come from the AWS documentation. exchange is on Athena's list of reserved words for DDL, so it needs backticks. And the partition key is dt and not day, because the CREATE TABLE page says a partition key with the same name as a table column is an error. The columns follow the data dictionary for Hiring Activity, and the {value: count} objects become maps.

create external table fokals_raw.company_hiring_daily (
  company_id           string,
  company              string,
  ticker               string,
  `exchange`           string,
  mic                  string,
  isin                 string,
  lei                  string,
  figi                 string,
  day                  string,
  open_postings        int,
  new_postings         int,
  closed_postings      int,
  by_function          map<string,int>,
  by_seniority         map<string,int>,
  by_country           map<string,int>,
  by_work_mode         map<string,int>,
  ai_postings          int,
  paid_media_postings  int,
  tech_mentions        map<string,int>,
  median_salary_usd    double
)
partitioned by (dt string)
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
location 's3://acme-robotics-data/fokals/raw/company_hiring_daily/'
tblproperties (
  'projection.enabled' = 'true',
  'projection.dt.type' = 'date',
  'projection.dt.format' = 'yyyy-MM-dd',
  'projection.dt.range' = '2026-10-01,NOW'
);

With partition projection on, Athena computes the partition list from the table properties instead of reading it from the AWS Glue Data Catalog. A new day is queryable as soon as its folder exists, with no MSCK REPAIR TABLE and no ALTER TABLE ADD PARTITION. Projected dates are generated in UTC, the time convention of every Fokals table. Two behaviours are worth knowing. Athena ignores any partition metadata registered for the table, and a day with no folder returns no rows instead of an error, so a failed download looks like a quiet day. Start the range on the first day you load.

Athena keeps the table definition in the AWS Glue Data Catalog. If you would rather have a crawler or a scheduled job register tables, the guide to AWS Glue pipelines for licensed data covers that route.

Query it

This query compares two closed days for each company and returns the open roles labelled as data science from the by_function map. Both dt filters matter: they confine the scan to two folders.

select
  t.company_id,
  t.open_postings - y.open_postings as change_in_open,
  element_at(t.by_function, 'data_science_ml') as data_science_roles
from fokals_raw.company_hiring_daily t
join fokals_raw.company_hiring_daily y
  on y.company_id = t.company_id
where t.dt = '2026-10-03'
  and y.dt = '2026-10-02'
order by change_in_open desc
limit 20;

Use element_at for map keys. Athena engine version 3 refers you to the Trino function reference, and in Trino a subscript on a key that is absent is an error, while element_at returns NULL. The dictionary describes by_function as open postings by label, as {value: count}, so check on a sample whether a function with no open roles is absent from the map, and read a NULL from element_at accordingly.

The folder name dt and the column day are different things: dt is the folder you chose, and day is the closed UTC day in the data. They agree when you name folders after the period in the manifest. Filter on day when the question is about the data, and on dt to limit the scan.

Times, weekly tables and missing days

Times in every Fokals table are UTC and ISO 8601 text, such as 2026-10-03T08:15:00Z. Declare them as string and parse them in a view with from_iso8601_timestamp, which reads ISO 8601, and wrap the call in try so that an empty value gives NULL and not an error. This view assumes a Job Postings table, job_postings, defined in the same way as the one above.

create or replace view fokals_raw.job_postings_typed as
select
  posting_id,
  company_id,
  try(from_iso8601_timestamp(first_seen_at)) as first_seen_at,
  try(from_iso8601_timestamp(closed_at)) as closed_at
from fokals_raw.job_postings;

Weekly tables take a seven-day step. For Sales Team Metrics, company_sales_weekly, whose week_start is a Monday, name each folder after the Monday of its week and use a partition key wk with the same type and format as dt. Set projection.wk.interval to 7 and projection.wk.interval.unit to DAYS, with a range that starts on a Monday such as 2026-09-28. For Market Series, market_series, whose as_of is a Sunday, start the range on a Sunday such as 2026-10-04. The step is counted from the start of the range, so a start on another weekday matches no folder.

Because a missing folder returns no rows and no error, look for gaps after every load. This query lists the closed days since the first day you loaded that have no rows at all. Replace the start date with your own, and run it once a day, because it reads every folder in the range.

select d as missing_day
from unnest(sequence(date '2026-10-01', date_add('day', -1, current_date))) as t(d)
where cast(d as varchar) not in (
  select day
  from fokals_raw.company_hiring_daily
  where dt >= '2026-10-01' and day is not null
  group by day
);

Convert to Parquet yourself

Fokals delivers CSV, JSON Lines or JSON, and the conversion to a columnar format is a step you run in Athena. The AWS pages on partitioning and on CTAS state that partitioning and columnar formats such as Parquet improve performance and reduce query cost, and that CTAS can do both in one statement. Convert the first day with CTAS and every later day with INSERT INTO, one day at a time, because Athena writes at most 100 partitions in one CTAS or INSERT INTO statement.

create table fokals_parquet.company_hiring_daily
with (
  format = 'PARQUET',
  parquet_compression = 'SNAPPY',
  partitioned_by = array['dt'],
  external_location = 's3://acme-robotics-data/fokals/parquet/company_hiring_daily/'
) as
select * from fokals_raw.company_hiring_daily
where dt = '2026-10-01';

insert into fokals_parquet.company_hiring_daily
select * from fokals_raw.company_hiring_daily
where dt = '2026-10-02';

A table's partition column comes last in a select *, which is where the AWS examples put it. CTAS refuses an external_location that already holds data, and the partitions it writes are added to the Data Catalog as it goes, so the Parquet table needs no projection. If a CTAS or INSERT INTO fails, partial files can be left behind and later queries can read them: delete them and run the statement again. Do not let the WHERE conditions of two INSERT INTO statements overlap, or the partitions they share hold duplicate rows.

Where this stops

Athena reads what is in the folders and nothing else. It cannot tell you that a day is missing, so compare the folders with the periods in the manifests you kept. Join to the companies you track on the company ID and count the companies matched in each table before you rely on a total. The same files can also be loaded into a warehouse; the guide to loading company data into Amazon Redshift shows that route, and the hiring dataset page lists the tables behind these examples.

Frequently asked questions

The queries and code on this page are examples to adapt. Test them in your own environment before you rely on them.

What this page says about the products it names was checked against their public documentation on 4 October 2026. Product and company names are trademarks of their owners. Fokals is not affiliated with them or endorsed by them.