Reminder: v201611 will be sunset on November 30, 2017.

The Publisher Query Language (PQL) Developer's Guide

PQL syntax and usage

PQL is a SQL-like language for querying for objects. The PQL syntax is similar to that of SQL, with a few differences described here. This section describes the PQL syntax, and how to use it to filter various object types.

The PQL syntax can be summarized as follows:

[WHERE <condition> {[AND | OR] <condition> ...}]
[ORDER BY <property> [ASC | DESC]]
[LIMIT {[<offset>,] <count>} | {<count> OFFSET <offset>}]

<condition> := <property> { = | != } <value>
<condition> := <property> { = | != } <bind variable>
<condition> := <property> IN <list>
<condition> := NOT <property> IN <list>
<condition> := <property> LIKE <wildcard%match>
<condition> := <property> IS NULL
<bind variable> := :<name>

Notes:

  • PQL keywords are not case sensitive.
  • Strings are escaped automatically when used in bind parameters. Otherwise:
    • For a string within single quotes (apostrophes), escape any additional apostrophe by writing it as a pair of single quotes.

      Example: "WHERE name = 'Company''s name'"

Keywords (case-insensitive)

  • WHERE - Expresses a set of zero or more conditions, optionally joined using AND or OR phrases. You can bundle AND or OR phrases with parentheses. Executing the query "" (empty string) returns everything.

    Examples: WHERE width = 728 ;   WHERE width = 728 AND height = 90 ;  WHERE (width = 728 AND height = 90) OR id IN (5008, 8745, 3487)

  • OR - Joins multiple conditions, only one of which must be true. If you want to check for any of several values for a single property, consider using an IN clause.

    Example: WHERE width = 728 OR height = 90

  • AND - Joins multiple conditions that must all be satisfied using the AND clause.

    Example: WHERE type = 'AGENCY' AND name IN ('CompanyNameA', 'CompanyNameB')

  • ORDER BY - Sorts the returned results in either ascending (ASC where 'A' is first) or descending (DESC where 'A' is last) order. If the direction is not specified, it defaults to ASC. If this clause is not included the default is ASC on the first field.

    Example: WHERE id IN (5008, 8745, 3487) ORDER BY id

  • LIMIT - The number of results to return. The LIMIT can also include an <offset>, which is how many rows from the start to offset your result set.

    Examples (both examples return the same result set): WHERE type = 'AGENCY' LIMIT 50 OFFSET 50 ;   WHERE type = 'AGENCY' LIMIT 50,50

  • OFFSET - The offset into the result set to begin returning values. Use this to page through results.

    Example (returns results 51-100): WHERE type = 'AGENCY' LIMIT 50 OFFSET 50.

  • <property> - One of the properties exposed by the object. Each object exposes different properties that you can filter by, using PQL; you usually cannot filter on all properties supported by an object, so check the list below to see which properties support PQL queries. For example, the creative properties you can filter by include id, name, width, and height.
  • <value> - String values should be quoted with a single (') quotation mark. Number values can be quoted or unquoted. Wildcards are not supported.
  • IN - Compares the value of a property to each item in a list; if any one matches, it is a positive match. The IN operator is equivalent to many = queries, one for each value, that are ORed together. The values are specified as a comma-separated list of values, enclosed in parentheses: (a, b, c). All of the values in the list are evaluated.

    Example: WHERE name IN ('CompanyNameA', 'CompanyNameB')

  • NOT IN - Compares the value of a property to each item in a list; if none match, it is a positive match. The NOT IN operator is equivalent to many != queries, one for each value, that are ORed together. The values are specified as a comma-separated list of values, enclosed in parentheses: (a, b, c). All of the values in the list are evaluated.

    Example: WHERE NOT name IN ('CompanyNameA', 'CompanyNameB')

  • LIKE - Enables you to query for objects using partial and wildcard string matching.

    Example: WHERE name LIKE 'startswith%'

  • IS NULL - Enables you to query for objects with an undefined property value. The classic example of this is querying for the root AdUnit by querying for an AdUnit with a null parent ID.

    Example: WHERE parentId IS NULL.

  • <bind variable> - You can use Value objects in place of hard-coded <value> values in your PQL query. A bind variable is referred to in PQL using a string name without spaces, starting with a : (colon).

    Example (Creates a query and enters two variables in place of hard-coded id and status property values):

    // Create two mapped parameters: id and status
    String_ValueMapEntry[] values = new String_ValueMapEntry[2];
    values[0] = new String_ValueMapEntry("id", new NumberValue(null, "123"));
    values[1] = new String_ValueMapEntry("status", new TextValue(null, "APPROVED"));
    
    // Create our statement and map our bind variables
    Statement statement = new Statement();
    statement.setQuery("WHERE id = :id AND status = :status LIMIT 500");
    statement.setValues(values);
  • DateTime fields - You can filter by date and time by assigning a DateTime value to a bind variable, or by using a string formatted according to ISO 8601.
    // Create two mapped parameters: id and status
    String_ValueMapEntry[] values = new String_ValueMapEntry[1];
    values[0] = new String_ValueMapEntry("startDateTime", new DateTimeValue(null, dateTime));
    
    // Create our statement and map our bind variables
    Statement statement = new Statement();
    statement.setQuery("WHERE endDateTime < '2012-01-01T00:00:00' AND startDateTime > :startDateTime LIMIT 500");
    statement.setValues(values);

Fetching match tables with PQL

Match tables provide a name-to-ID lookup for the raw values contained within data transfer files, allowing you to match ad serving information (such as ad unit or line item) to pre-assigned values stored in the database. For example, a data transfer event might record that a line item with ID 12345678 served on 01-6-2011 at 16:10:10. The line item match table lets you know the name of that line item, as well as its start date, end date, and other useful attributes.

You can easily create your own match table scripts that include only the fields and range of objects you need using the DFP API. An example set of Python match table scripts follows. Additionally, each of the DFP API client libraries have examples of GetAllX for each service that can be adjusted to output to files or databases to fulfill this need if you aren't using Python.

Python


  1. Set up your credentials

    Populate the templated fields in the googleads.yaml file, then call DfpClient.LoadFromStorage() to initialize the Client object.

      # Initialize client object.
      dfp_client = dfp.DfpClient.LoadFromStorage()
    
  2. Construct a DataDownloader object
      # Initialize a report downloader.
      report_downloader = client.GetDataDownloader(version='v201705')
    
  3. Set up your query statements and file handles

    After you've instantiated your client and created the Report Downloader, create the file handles to write your match tables to as well as the query statements to filter on.

      line_items_file = tempfile.NamedTemporaryFile(
          prefix='line_items_', suffix='.csv', mode='w', delete=False)
      ad_units_file = tempfile.NamedTemporaryFile(
          prefix='ad_units_', suffix='.csv', mode='w', delete=False)
    
      line_items_pql_query = ('SELECT Name, Id, Status FROM Line_Item ORDER BY Id '
                              'ASC')
      ad_units_pql_query = 'SELECT Name, Id FROM Ad_Unit ORDER BY Id ASC'
  4. Pass the query and file handle to DownloadPqlResultToCsv
    
    
      # Downloads the response from PQL select statement to the specified file
      report_downloader.DownloadPqlResultToCsv(
          line_items_pql_query, line_items_file)
      report_downloader.DownloadPqlResultToCsv(
          ad_units_pql_query, ad_units_file)
    

Enviar comentarios sobre…

DoubleClick for Publishers
DoubleClick for Publishers
¿Necesitas ayuda? Visita nuestra página de asistencia.