Paramount leverages a multi-CDN strategy to stream their videos to end-users. In order to monitor end-user experience and ensure the highest quality video delivery, Paramount ingests logs from multiple CDNs, origins, and player analytics data sources. Their goal is to provide realtime visibility into a viewer’s quality of experience, enable root cause analysis of stream-impacting issues, and equip operations teams to debug aggregate data as well as individual stream sessions.
Paramount came to Hydrolix with the challenge to build a solution which could:
- support real-time data streaming
- automatically scale to their needs
- allow data enrichment via custom business logic
- minimize system complexity and operational overhead
- and reduce overall costs even while increasing data retention and usage
In this series of blog post we’ll explore how Paramount leveraged Hydrolix to tackle all of these challenges.
Multiple Sources -> Single Table
One of the biggest technical downsides to a multi-CDN strategy is that each vendor delivers logs in a proprietary format using proprietary connectors. In order to make sense of all this data Paramount needed a way to transform all of these diverse data feeds into a single, normalized table.
Hydrolix multi-schema tables allow for multiple sources and transforms. This flexibility makes it very easy to integrate data from multiple sources and format into a single, unified view. For example, one can have a single table where one data-set is delivered via HTTP streaming and another via Kafka, each with different columns names.
Paramount deployed a dedicated Hydrolix for GKE cluster in their GCP account, created a table for normalized CDN logs, published separate transforms for each CDN vendor, and then configured each of their vendors to send data to the Paramount Hydrolix cluster. These transforms convert various raw data-feeds into a unified Paramount standard.
Paramount also leverages advanced function such as dictionary lookup to enrich incoming stream data with business labels and metrics.

To see more details on real time logs delivery for each CDN you can have a look here:
Standardization
Each CDN can express the same data with multiple column name.
For example http status code have different name for every CDN, and the standard define by Paramount.
CDN | Akamai | Edgecast | Fastly | Standard |
Field Name | statusCode | status_code | status | status_code |
Visor Field | Definition | Cloudfront | Edgecast | Fastly | Akamai | |
account_number | Account Number | Derived from the first sub path of rewritten_path | CPCode | |||
backend_ip | IP of the “backend”, either an origin or upstream CDN tier | beresp.backend.ip | ||||
backend_ttlb | Time To Last Byte in milliseconds of the backend origin or upstream CDN. The time taken from edge server forwarding request to backend machine to the time the edge server recieved and cached the response. | read_time * 1000 | Custom var.backend_ttlb variable based on time.elapsed.msec from vcl_fetch to vcl_log divided by 1000 | |||
business_unit | CBS Business Unit which owns the workflow | Visor Metadata | Visor Metadata | Visor Metadata | Visor Metadata | Visor Metadata |
bytes_in | Bytes sent from client to the CDN in the request | cs-bytes | bytes_in | req.bytes_read | n/a | httpRequest.requestSize |
bytes_out | Bytes sent from CDN to the client in the response | sc-bytes | bytes_out | resp.bytes_written | bytes | httpRequest.responseSize |
cache_shield | fastly.ff.visits_this_service > 0 | |||||
cache_status | Simplification of the X-Cache header for comparison with other CDNs. True indicates the word ‘HIT’ was found in the X-Cache header and indicates some kind of origin offload. (Ex: if the cache expired but the origin returned a 304, the origin didn’t have send back the full body) | cache_status | fastly_info.state | cacheStatus | ||
cached | Simplification of cache_status. Boolen summarizing cached as true or false | derived from x-edge-response-result-type | Derived from cache_status. TCP_HIT or TCP_PARTIAL_HIT makes this true. Otherwise false. | Derived from fastly_info.state if it contains “HIT” | derived from cache_status | derived from jsonPayload.cacheStatus |
client_asn | The autonomous system number sent from the client request | client_asn | client.as.number | Now added as custom field | ||
client_geo_country | The 2 letter country code from where the client sent the request | c-country | client_geo_country | client.geo_country_code | country | jsonPayload.clientRegionCode |
client_ip | Client IP address from where the request was sent | c-ip | client_ip | req.http.Fastly-Client-IP | client_ip (cliIP) | httpRequest.remoteIP |
host | The host name the client requested | cs-host | host | req.http.host | Request Host (reqHost) | httpRequest.requestUrl |
client_ttfb | The time to first byte | time-to-first-byte | n/a | client_ttfb | Turnaround time | edgecache.googleapis.com/edge_cache_route_rule/http_ttfb_by_client |
environment | A field to filter out dev, qa, prod data | Visor Metadata | Visor Metadata | Visor Metadata | Visor Metadata | Visor Metadata |
extension | The file extension of the file requested | Derived from path | req.url.ext | |||
forward_for | The clients original ip address that was forwarded within the request | x-forwarded-for | x-forwarded-for (custom field from header) | forward_for | X-Forwarded-For (xForwardedFor) | httpRequest.remoteIP |
failover_status | Shield header to specify if a request retried in another region | resp.status | ||||
if_modified_since | The value of the request header if-modified-since | req.http.If-Modified-Since | ||||
if_none_match | The value of the if-none-match header | req.http.If-None-Match | ||||
if_unmodified_since | The value of the if-unmodified-since header | req.http.If-Unmodified-Since | ||||
midgress | The intra-CDN tier the content was served from | midgress | derived from cache_status | |||
method | http request method | cs-method | method | method | Request Method (reqMethod) | httpRequest.requestMethod |
path | request path | cs-uri-stem | path | req.url.path | Request Path (reqPath) | httpRequest.requestUrl |
pop | The CDN POP (airport/city code) , should be prefaced with city | x-edge-location | pop | server.datacenter | Now added as custom field | jsonPayload.metroIataCode |
query | The query string parameter within the path of a request | cs-uri-query | query | req.url.qs | query string (queryStr) | httpRequest.requestUrl |
origin_request_id | A hash generated request identifier returned from the origin. | custom field, derived from x-req-id header returned from origin | ||||
range_request | “Range” request header used by a client to make a byte-range request for a portion of an asset | req.http.Range | range? | |||
range_response | Value of the Range response header after client made a byte range request | sc-range-start + sc-range-end | resp.http.Range | |||
referer | The address of where the request was made from | cs-referer | referer | req.http.Referer | referer | httpRequest.referer |
request_id | A hashed id to trace a request from edge, shield, and origin | request_id | request_id | request_id | request_id (reqId) | jsonPayload.requestId |
role | Used to help determine the edge, shield, or shield regions | Visor Metadata | Visor Metadata | Visor Metadata | Visor Metadata | Visor Metadata |
retrans | The number of packets in the current connection that contained data being retransmitted counted across the lifetime of the connection. | client.socket.tcpi_total_retrans | ||||
rtt_msecs | TCP roundtrip time in milliseconds | client.socket.tcpi_rtt / 1000 | ||||
server_ip | IP of the edge node that serves the content from the CDN | server_ip | server.ip | Edge IP — ghost_ip | ||
server_ttlb | Time to Last Byte in milliseconds from the server. This is the total time Fastly spent processing the request including any backend request. The server’s TTLB should capture the latency of processing and serving the request from the CDN perspective. | time_taken | total_time | time.elapsed.usec / 1000 | (request end time – request time) + transfer time | edgecache.googleapis.com/edge_cache_route_rule/http_ttlb_by_client |
status_code | The HTTP status code returned | sc-status | status | resp.status | HTTP Status Codes | httpRequest.status |
stream | A friendly name used to identify a live stream | visor metadata | visor metadata | visor metadata | visor metadata | visor metadata |
timestamp | UTC time in 100s of nanoseconds for when the server first received the request. If clocks were synchronized, should be later than or equal to when the player sent the request. | timestamp | timestamp | timestamp | request_time | jsonPayload.receiveTimestamp |
user_agent | Identifier of the browser or device that made the request | cs-user-agent | user_agent | req.http.User-Agent | user-agent | httpRequest.userAgent |
workflow | An organizational name that groups a live or vod video workflow | Visor Metadata | Visor Metadata | Visor Metadata | Visor Metadata | Visor Metadata |
session_id | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | |
buffer length | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | |
buffer starvation | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | |
Measured Throughput | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | |
Content ID | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string | CMCD field, derived from query string |
In order get the proper name, we can use the function from_input_field
in transform:
1 2 3 4 5 6 7 8 9 |
{ "name": "status_code", "datatype": { "type": "uint32", "source": { "from_input_field": "statusCode" } } } |
This means that we are creating a column status_code
by using the source field statusCode
.
We use the same principle for each CDN and different columns.
For Fastly this would be the following transform:
1 2 3 4 5 6 7 8 9 |
{ "name": "status_code", "datatype": { "type": "uint32", "source": { "from_input_field": "status" } } } |
To help identify each CDN in the destination table Paramount also creates a new virtual column source
containing the CDN name:
1 2 3 4 5 6 7 8 |
{ "name": "source", "datatype": { "type": "string", "default": "edgecast", "virtual": true } } |
We have added a list of transform in our github repository to provide working examples of each of these CDNs, and will be adding more shortly:
https://github.com/hydrolix/transforms
Custom Enrichment
CDN logs contains important information, adding external information allows Paramount to see not only bytes delivered but more details about streaming session ID. By extracting stream id from path and query parameters and correlate those with an in memory dictionary.
In addition to renaming and remapping column, Hydrolix allows users to write SQL statement for the streaming data coming into the platform. This allows users to do some advanced modification and apply additional business notations to the indexed information.
For example, in the Akamai transform Paramount applies the following sql_transform
:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT concat('cpc_', account_number) as account_number, concat('/', path) as path, splitByChar(',',decodeURLComponent(assumeNotNull(customField))) as custom_array, custom_array[1] as stream_id, custom_array[2] as pop, custom_array[3] as client_asn, (request_end_time_msec + transfer_time_msec) as server_ttlb, visordb_visor_path_prefix(assumeNotNull(path),assumeNotNull(host)) as path_prefix, decodeURLComponent(assumeNotNull(user_agent)) as user_agent, dictGet('visor_akamai_tags', ('stream', 'stream_config_id', 'environment', 'workflow', 'business_unit'),(account_number, path_prefix)) as meta, tupleElement(meta, 'stream') as stream, tupleElement(meta, 'stream_config_id') as stream_config_id, tupleElement(meta, 'environment') as environment, tupleElement(meta, 'workflow') as workflow, tupleElement(meta, 'business_unit') as business_unit, NOT cached as midgress, now() as auto_receive_time, * FROM {STREAM} |
Akamai logs contains a customField
, here we are splitting the custom field into an array using the character ,
Then we take the different value of that array and assign those into new column.
1 2 3 4 |
splitByChar(',',decodeURLComponent(assumeNotNull(customField))) as custom_array, custom_array[1] as stream_id, custom_array[2] as pop, custom_array[3] as client_asn |
For this value "customField": "XXXXXXX,38458,15169"
we’ll create 3 columns:
- stream_id:
XXXXXXX
- pop:
38458
- client_asn:
15169
We also created a custom function which extracts specific information from the path of the content:
1 2 3 4 5 6 7 |
visordb_visor_path_prefix(assumeNotNull(path),assumeNotNull(host)) as path_prefix, dictGet('visor_akamai_tags', ('stream', 'stream_config_id', 'environment', 'workflow', 'business_unit'),(account_number, path_prefix)) as meta, tupleElement(meta, 'stream') as stream, tupleElement(meta, 'stream_config_id') as stream_config_id, tupleElement(meta, 'environment') as environment, tupleElement(meta, 'workflow') as workflow, tupleElement(meta, 'business_unit') as business_unit, |
And with a dictionary file Paramount is able to apply real-time lookups to enrich their incoming data, improving indexing and reducing the need to apply expensive joins at query-time.
Technically we are getting a tuple from the dictionary lookup and we are then extracting from that tuple the different columns.
Some basic SQL mathematical function allows us to calculate the server_ttlb
as the sum of (request_end_time_msec + transfer_time_msec)
And finally we are adding the auto_receive_time
as the time function now()
.
Summary
The paramount PoC team estimates that they reduced their infrastructure spend by over 50%, increase their data retention period 6x.
Another Paramount use case simplified their logging pipeline from a complex, multi-step process to a single autoscaling platform, and enabled them to do complex data enrichment in realtime.
In our next blog post we’ll discuss how Paramount’s query and dashboard performance has improved thanks to Hydrolix.