2.1_complex_large_queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e2ce2076-d368-426d-815d-647270f1141e",
   "metadata": {},
   "source": [
    "# Building Queries for Snapshots\n",
    "\n",
    "Designing a query is the most important part of data selection. It can have implications on how relevant the selected data is for the workflow.\n",
    "\n",
    "To guide in this process, Snapshot operations use the `SnapshotQuery` class.\n",
    "\n",
    "# SnapshotQuery class\n",
    "A query is at its minimum a where statement. However, this class offers additional attributes to support specific operations like Analytics or Extraction. A detailed description of the syntax, fields and potential values can be seen in the [Data Selection Samples - Dow Jones Developer Portal](https://developer.dowjones.com/site/docs/getting_started/data_selection_samples/index.gsp).\n",
    "\n",
    "* **`where`**: _Required_, _Positional_. String that specifies the Where statement as used in Google BigQuery SQL Sytnax.\n",
    "* **`includes`**: _Optional_, _Default: `None`_. Object-type syntax that specifies the list of fields and values to include in bulk. Value can be set as ```{\"company_codes\": ['mcrost', 'applc'] }```\n",
    "* **`excludes`**: _Optional_, _Default: `None`_. Object-type syntax that specifies the list of fields and values to exclude in bulk. Value can be set as ```{\"subject_codes\": ['gspo', 'nnam'] }```\n",
    "* **`frequency`**: _Optional_, _Default: `'MONTH'`_. Frequency to aggregate results. Possible values are `'MONTH'`, `'YEAR'` or `'DAY'`. Used only by the Analytics operation.\n",
    "* **`date_field`**: _Optional_, _Default: `'publication_datetime'`_. Date-time field used to aggregate. Possible values are `'publication_datetime'`, `'ingestion_datetime'` and `'modification_datetime'`. Used only by the Analytics operation.\n",
    "* **`group_by_source_code`**: _Optional_, _Default: `False`_. Specifies if the results will contain only a year field (`False`), or if each year will be broken-down by source (`True`). Used only by the Analytics operation.\n",
    "* **`top`**: Valid only if ```group_by_source_code``` is True. Limits the number of sources used to group the content by frequency. Used only by the Analytics operation.\n",
    "* **`file_format`**: _Optional_, _Default: `'avro'`_. File format to be used for Extractions. Possible values are `'avro'`, `'csv'` or `'json'`. Used only by the Extraction operation.\n",
    "* **`limit`**: _Optional_, _Default: `0` (No limit)_. Positive integer that limits the amount of documents to extract. Used only by the Extraction operation.\n",
    "\n",
    "This class is described in more detail in the [package documentation](https://factiva-news-python.readthedocs.io/en/latest/factiva.news/snapshot.html#snapshot-query)."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c6ddd95b-a6af-44ff-9fdd-966a525673eb",
   "metadata": {},
   "source": [
    "# Query Snippets\n",
    "\n",
    "## Where Attribute\n",
    "The where attribute is the most used attribute to select content by applying different conditions to the available fields. Below there are some code snippets that help building a where statement, and notes that help modify the syntax according to the need.\n",
    "\n",
    "### Range of dates\n",
    "It works with the standard SQL syntax. If updates will be collected in the future, avoid using an end-date.\n",
    "Alternative fields are: `modification_datetime` and `ingestion_datetime`.\n",
    "```sql\n",
    "publication_datetime >= '2010-01-01 00:00:00' AND publication_datetime <= '2020-06-30 23:59:59' \"\n",
    "```\n",
    "\n",
    "### Filter by `source_codes`\n",
    "The following clause is useful to select sources by their individual code.\n",
    "```sql\n",
    "UPPER(source_code) IN ('AASFNE', 'HTACCF', 'NLADLW', 'ADVTSR', 'AFNROL', 'AGEEOL', 'AGEE', 'HNASNI', 'APRS', 'ASXTEX', 'AUSTOL')\n",
    "```\n",
    "In case sources will be selected by their category or source family, a better option is using `restrictor_codes`. This field is not in the documentation, but the CSE or Integration Team can provide more information like source family codes\n",
    "```sql\n",
    "REGEXP_CONTAINS(restrictor_codes, r'(?i)(^|,)(sfwsj|sflefig|sfechos|sfnyt)($|,)')\n",
    "```\n",
    "\n",
    "### Filter by `subject_codes`\n",
    "```sql\n",
    "REGEXP_CONTAINS(subject_codes, r'(?i)(^|,)(mcat|ccat|ecat|gglobe|ghea|ghnwi|gcns|gpir|gdatap|greest|grisk|gsci|gspace|gtrans)($|,)')\n",
    "```\n",
    "\n",
    "### Filtering by `language_code`\n",
    "```sql\n",
    "LOWER(language_code) IN ('en', 'es', 'it')\n",
    "```\n",
    "\n",
    "### Filtering by `company_codes_xxxx`\n",
    "This is applicable to any company-related fields (about, occur or company_codes and other combinations with identifiers - ISIN, CUSIP...).\n",
    "- company_codes\n",
    "- company_codes_about\n",
    "- company_codes_occur\n",
    "- ...\n",
    "- company_codes_about_isin\n",
    "- ...\n",
    "\n",
    "#### Short list of codes\n",
    "```sql\n",
    "REGEXP_CONTAINS(company_codes, r'(?i)(^|,)(agbpet|agip|agphng|agpnme|agzgi|altgaz|bbor|brnene|distrg|eenivm|egapg|enichm|enie|enimnt)($|,)')\n",
    "```\n",
    "\n",
    "#### At least 1 code\n",
    "In case the interest is to ensure at least one company is tagged (the field is not empty), the expressions looks like this.\n",
    "```sql\n",
    "LENGTH(company_codes) > 2\n",
    "```\n",
    "\n",
    "#### Exactly X codes\n",
    "When searching for an exact number of tagged companies, it's necessary to count the number of entries in the resulting array after splitting the string.\n",
    "```sql\n",
    "ARRAY_LENGTH(SPLIT(company_codes_about, ',')) = 3\n",
    "```\n",
    "Use `n+2` depending on the `n` companies looking to be tagged (3 for 1 code, 4 for 2 codes, 5 for 3 codes, and so on).  This example selects articles with exactly 1 company tagged as `company_codes_about`.\n",
    "\n",
    "#### Long list of codes\n",
    "When having a long list of FCodes in an external file like CSV, adding all values to the regex expression requires the use of programming code. In this case this shows how to do it in Python.\n",
    "```python\n",
    "import pandas as pd\n",
    "\n",
    "comp_list = pd.read_csv('fcodes_list.csv')\n",
    "comp_list_regex = \"\"\n",
    "\n",
    "for index, fcode in comp_list['fcode'].items():\n",
    "    if index == 0:\n",
    "        comp_list_regex = f\"{fcode}\"\n",
    "    else:\n",
    "        comp_list_regex = f\"{comp_list_regex}|{fcode}\"\n",
    "\n",
    "where_statement = f\"REGEXP_CONTAINS(company_codes_about, r'(?i)(^|,)({comp_list_regex})($|,)')\"\n",
    "\n",
    "```\n",
    "\n",
    "### Filtering by `industry_codes`\n",
    "```sql\n",
    "REGEXP_CONTAINS(industry_codes, r'(?i)(^|,)(i1|i25121|i2567)($|,)')\n",
    "```\n",
    "\n",
    "### Filtering by `person_codes`\n",
    "```sql\n",
    "REGEXP_CONTAINS(LOWER(person_codes), r'(?i)(^|,)(76064380|2349856)($|,)\n",
    "```\n",
    "\n",
    "### Filtering by the **region** the article is **about** (`region_codes`)\n",
    "```sql\n",
    "REGEXP_CONTAINS(region_codes, r'(?i)(^|,)(aust|spain|italy|usa|uk)($|,)')\n",
    "```\n",
    "\n",
    "### Filtering by the **region** where the source is **headquarted** (`region_of_origin`)\n",
    "```sql\n",
    "REGEXP_CONTAINS(region_of_origin, r'(?i)(aust|spain|italy|usa|uk)')\n",
    "```\n",
    "\n",
    "### Filtering by the number of words (`word_count`)\n",
    "```sql\n",
    "word_count >= 250\n",
    "```\n",
    "\n",
    "### Filtering by terms in full-text (Keyword search)\n",
    "```sql\n",
    "REGEXP_CONTAINS(CONCAT(title, ' ', IFNULL(snippet, ''), ' ', IFNULL(body, '')), r'(?i)(^|\\b)(economic|economy|regulation|deficit|budget\\W+tax|central\\W+bank)($|.|\\b)')\n",
    "```\n",
    "\n",
    "### Filtering Non-Latin Text Keywords (e.g., CJK Characters)\n",
    "Regular expression word boundaries (\\b) may not work effectively with Non-Latin characters. The LIKE operator is recommended instead.\n",
    "\n",
    "Simple Example (Searching for 経済):\n",
    "\n",
    "```sql\n",
    "CONCAT(title, ' ', IFNULL(snippet, ''), ' ', IFNULL(body, '') ) LIKE '%経済%'\n",
    "```\n",
    "\n",
    "Complex Example (Searching for multiple Japanese keywords):\n",
    "\n",
    "```sql\n",
    "CONCAT(title, ' ', IFNULL(snippet, ''), ' ', IFNULL(body, '')) LIKE '%日本語キーワード１%' OR CONCAT(title, ' ', IFNULL(snippet, ''), ' ', IFNULL(body, '')) LIKE '%日本語キーワード２%' OR CONCAT(title, ' ', IFNULL(snippet, ''), ' ', IFNULL(body, '')) LIKE '%日本語キーワード３%'\n",
    "```\n",
    "\n",
    "\n",
    "More examples are available in the Data Selection Samples in the Dow Jones Developer Portal (https://developer.dowjones.com/site/docs/getting_started/data_selection_samples/index.gsp#)\n",
    "\n",
    "\n",
    "Building the where statement. Python concatenates the strings when inside the parenthesis. Mind the extra space at the end of each string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "92975ce2-d0f7-4ec6-ae6a-372cf4e80db9",
   "metadata": {},
   "outputs": [],
   "source": [
    "where_statement =  (\n",
    "                    r\" publication_datetime >= '2020-01-01 00:00:00' \"\n",
    "                    r\" AND publication_datetime <= '2021-04-30 23:59:59' \"\n",
    "                    r\" AND REGEXP_CONTAINS(CONCAT(title, ' ', IFNULL(snippet, ''), ' ', IFNULL(body, '')), r'(?i)(^|\\b)(economic|economy|regulation|deficit|budget\\W+tax|central\\W+bank)($|.|\\b)') \"\n",
    "                    r\" AND REGEXP_CONTAINS(subject_codes, r'(?i)(^|,)(cprdcl|c16|cbro)($|,)') \"\n",
    "                    r\" AND REGEXP_CONTAINS(region_codes, r'(?i)(^|,)(spain|italy)($|,)') \"\n",
    " )"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}