Query BigQuery Data using the OData SDK for PHP

The CData API Server, paired with the BigQuery connector (or any of 300+ supported data sources), exposes BigQuery data as web services. This article walks you through setting up the CData API Server and demonstrates the process to work with BigQuery data as PHP objects.

Getting Started

---

Setting Up the API Server

If you haven't already, download and install the CData API Server. After installation, run the application, download the BigQuery connector from within the API Server, and configure your BigQuery data connection. Then, configure API Server to generate OData feeds for any tables you want to access in your single-page application (SPA).

Enable CORS

AngularJS requires servers to have CORS (Cross-Origin Resource Sharing) enabled. To enable CORS, go to the Server tab under the Settings section in the API Server and adjust the following settings:

• Click the checkbox to Enable cross-origin resource sharing (CORS).
• Either click the checkbox to Allow all domains without '*' or specify the domain(s) that are allowed to connect in Access-Control-Allow-Origin.
• Set Access-Control-Allow-Methods to "GET, PUT, POST, OPTIONS".
• Set Access-Control-Allow-Headers to "Content-Type, Authorization".
• Click Save to save the changes made.

Connect to BigQuery

To work with BigQuery data using AngularJS, we start by creating and configuring a BigQuery connection. Follow the steps below to configure the API Server to connect to BigQuery data:

1. First, navigate to the Connections page.
2. Click Add Connection and then search for and select the BigQuery connection.
3. Enter the necessary authentication properties to connect to BigQuery.
4. After configuring the connection, click Save & Test to confirm a successful connection.

Configure API Server Users

Next, create a user to access your BigQuery data through the API Server. You can add and configure users on the Users page. Follow the steps below to configure and create a user:

1. On the Users page, click Add User to open the Add User dialog.
2. Next, set the Role, Username, and Privileges properties and then click Add User.
3. An Authtoken is then generated for the user. You can find the Authtoken and other information for each user on the Users page:

Creating API Endpoints for BigQuery

Having created a user, you are ready to create API endpoints for the BigQuery tables:

1. First, navigate to the API page and then click Add Table .
2. Select the connection you wish to access and click Next.
3. With the connection selected, create endpoints by selecting each table and then clicking Confirm.

Gather the OData URL

Having configured a connection to BigQuery data, created a user, and added resources to the API Server, you now have an easily accessible REST API based on the OData protocol for those resources. From the API page in API Server, you can view and copy the API Endpoints for the API:

Just like with standard OData feeds, you can limit the fields returned by adding a $select parameter to the query. You can also include other standard OData URL parameters, such as $filter, $orderby, $skip, and $top. Refer to the help documentation for more details on supported OData queries.

Save the Authtoken

Select the user you created, then copy and save the authtoken for future use. You can also toggle on 'Token Expiration' and set the number of days based on your use case. Click Save to save the details.

Install OData SDK for PHP

Run Terminal or Command Prompt as an Administrator, and type in the given commands to install OData SDK for PHP on your system. Change your system's current working directory to the "D:" drive.

Clone the odataphp repository from GitHub into a folder named "odataphp" on the D:\ drive.

Configure PHP

1. Inside the extracted PHP directory, you’ll find the sample configuration files: "php.ini-development" and "php.ini-production".
2. Copy the file that best suits your needs and rename the copy to "php.ini".
3. Open the php.ini file in a text editor and make the following changes:
   • extension_dir = "ext" to tell PHP where to find its extensions.
   • Enable the necessary extensions by removing the semi-colon (;) at the beginning of the line (e.g., remove the ";" from ;extension = mysqli to enable MySQLi support). You'll find the following lines under the Dynamic Extensions section of the php.ini file:
     • extension = php_curl.dll
     • extension = php_xsl.dll
   • Make the following changes under the Path and Directories section of the php.ini file:
     • ; Windows: "\path1;\path2"
     • ;include_path = ".;D:\odataphp"

Add PHP and ODataPHP to the system path

1. Press Win+R on your windows system, type sys.dll, and hit Enter.
2. Go to the Advanced tab and click Environment Variables.
3. In the System variables section, find the "Path" variable and click Edit.
4. Click Newand add the path to your PHP directory (e.g., D:\odataphp and D:\php)
5. Click OK to all the dialogs.
6. Restart the Terminal or Command Prompt to apply the changes.

Work with BigQuery Data using OData API in PHP

This section outlines the process of accessing and manipulating BigQuery data using a PHP application and an OData API obtained from the CData API Server. The workflow involves defining PHP classes that mirror BigQuery entities, utilizing an API client to communicate with the OData API server, creating new records, and retrieving data for display.

Metadata and PHP Class Generation

The initial step involves obtaining metadata from the CData API Server, which reflects the structure of the BigQuery entities (tables). To obtain the metadata, open your browser and go to the metadata URL of the CData API Server: http://localhost:8080/api.rsc/$metadata

The given URL will provide you with metadata in XML format about all the available tables and data models exposed by the CData API Server. This metadata is then used to define corresponding PHP classes. The Demo_Db_Crm_Account.php file exemplifies this, where the Demo_Db_Crm_Account class is created to represent the DEMO_DB_CRM_Account entity in BigQuery. Each property of the class corresponds to a column in the BigQuery table.

Demo_Db_Crm_Account.php

class Demo_Db_Crm_Account {
    public $Id;
    public $AccountNumber;
    public $AccountSource;
    public $Active__c;
    public $AnnualRevenue;
    public $BillingCity;
    public $BillingCountry;
    public $BillingGeocodeAccuracy;
    public $BillingLatitude;
    public $BillingLongitude;
    public $BillingPostalCode;
    public $BillingState;
    public $BillingStreet;
    public $CleanStatus;
    public $CreatedById;
    public $CreatedDate;
    public $CustomerPriority__c;
    public $DandbCompanyId;
    public $Description;
    public $DunsNumber;
    public $Fax;
    public $Industry;
    public $IsDeleted;
    public $Jigsaw;
    public $JigsawCompanyId;
    public $LastActivityDate;
    public $LastModifiedById;
    public $LastModifiedDate;
    public $LastReferencedDate;
    public $LastViewedDate;
    public $MasterRecordId;
    public $NaicsCode;
    public $NaicsDesc;
    public $Name;
    public $NumberOfEmployees;
    public $NumberofLocations__c;
    public $OwnerId;
    public $Ownership;
    public $ParentId;
    public $Phone;
    public $PhotoUrl;
    public $Rating;
    public $ShippingCity;
    public $ShippingCountry;
    public $ShippingGeocodeAccuracy;
    public $ShippingLatitude;
    public $ShippingLongitude;
    public $ShippingPostalCode;
    public $ShippingState;
    public $ShippingStreet;
    public $Sic;
    public $SicDesc;
    public $Site;
    public $SLA__c;
    public $SLAExpirationDate__c;
    public $SLASerialNumber__c;
    public $SystemModstamp;
    public $TickerSymbol;
    public $Tradestyle;
    public $Type;
    public $UpsellOpportunity__c;
    public $Website;
    public $YearStarted;

    public function toArray() {
        $reflection = new ReflectionClass($this);
        $props = $reflection->getProperties(ReflectionProperty::IS_PUBLIC);
        $array = [];

        foreach ($props as $prop) {
            $name = $prop->getName();
            $array[$name] = $this->$name;
        }

        return $array;
    }
}

Creating Records in BigQuery

The add_account.php script demonstrates the creation of new records in the BigQuery database. It instantiates the Demo_Db_Crm_Account class, populates its properties with the data for the new record, and then uses the API client to send this data to the CData API Server. The API server translates this request into the necessary BigQuery operations to insert the new record into the DEMO_DB_CRM_Account table.

add_account.php

require_once 'ApiClient.php'; // Adjust path if needed
require_once 'Demo_Db_Crm_Account.php';

// API setup
$client = new ApiClient("http://localhost:8080/api.rsc", "your_username", "your_authtoken");

// Create a new account object
$account = new Demo_Db_Crm_Account();
$account->Id = uniqid(); // or use fixed ID like "1"
$account->AccountNumber = "123891";
$account->AccountSource = "Direct";
$account->BillingCity = "New York";
$account->BillingPostalCode = "14561";
$account->Name = "Company1";
$account->Phone = "9889543434";
$account->AnnualRevenue = 67890566;

// Convert object to array using Reflection
$data = $account->toArray();

// Show request payload (optional)
echo "Request JSON:\n";
echo json_encode($data, JSON_PRETTY_PRINT);

// Post to BigQuery through CData OData API Server
$response = $client->post("DEMO_DB_CRM_Account", $data);

echo "\n\nResponse:\n";
print_r($response);

Create a new record in BigQuery using the given command:

API Client Abstraction

The ApiClient.php file defines the ApiClient class, which serves as an abstraction layer for interacting with the CData API Server. This class encapsulates the logic for making GET, POST, PUT, and DELETE requests, handling authentication, and processing responses. By using the ApiClient, the application code is shielded from the complexities of the OData protocol and the underlying HTTP communication. This promotes cleaner, more maintainable code.

ApiClient.php

if (!class_exists('ApiClient')) {
    class ApiClient {
        private $baseUrl;
        private $username;
        private $authtoken;

        public function __construct($baseUrl, $username, $authtoken) {
            $this->baseUrl = rtrim($baseUrl, '/');
            $this->username = $username;
            $this->authtoken = $authtoken;
        }

        private function getHeaders() {
            return [
                "Content-Type: application/json",
                "Accept: application/json",
                "Authorization: Basic " . base64_encode("{$this->username}:{$this->authtoken}")
            ];
        }

        public function post($entity, $data) {
            $url = "{$this->baseUrl}/{$entity}";
            $payload = json_encode($data);

            $ch = curl_init($url);
            curl_setopt($ch, CURLOPT_HTTPHEADER, $this->getHeaders());
            curl_setopt($ch, CURLOPT_POST, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $payload);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

            $response = curl_exec($ch);

            if (curl_errno($ch)) {
                throw new Exception("cURL Error: " . curl_error($ch));
            }

            curl_close($ch);

            // *** DEBUG: Log the raw response ***
            error_log("Raw POST Response: " . $response);

            // Ensure we decode the JSON response
            $decodedResponse = json_decode($response, true);
            if ($decodedResponse === null && json_last_error() !== JSON_ERROR_NONE) {
                throw new Exception("JSON Decode Error: " . json_last_error_msg());
            }
            return $decodedResponse;
        }

        public function get($entity) {
            $url = "{$this->baseUrl}/{$entity}";

            $ch = curl_init($url);
            curl_setopt($ch, CURLOPT_HTTPHEADER, $this->getHeaders());
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

            $response = curl_exec($ch);

            if (curl_errno($ch)) {
                throw new Exception("cURL Error: " . curl_error($ch));
            }

            curl_close($ch);

            // *** DEBUG: Log the raw response ***
            error_log("Raw GET Response: " . $response);

            // Ensure we decode the JSON response
            $decodedResponse = json_decode($response, true);
            if ($decodedResponse === null && json_last_error() !== JSON_ERROR_NONE) {
                throw new Exception("JSON Decode Error: " . json_last_error_msg());
            }
            return $decodedResponse;
        }
    }
}

Data Retrieval and Display

The get_data.php script retrieves data from the BigQuery DEMO_DB_CRM_Account entity and displays it. It uses the ApiClient to send a GET request to the CData API Server. The server retrieves the data from BigQuery, and the script then processes the response, mapping the data to Demo_Db_Crm_Account objects. Finally, the script iterates through these objects to display the account information.

get_data.php

require_once 'ApiClient.php';
require_once 'Demo_Db_Crm_Account.php';

// API setup
$baseUrl = "http://localhost:8080/api.rsc";
$username = "your_username";
$authtoken = "your_authtoken";

// Entity to retrieve
$entity = "DEMO_DB_CRM_Account";

// Fetch data
try {
    $client = new ApiClient($baseUrl, $username, $authtoken);
    $response = $client->get($entity);

    if ($response && is_array($response)) {
        echo "<h2>Data from \{$entity}</h2>\n";

        // Map the data to Account objects
        $accounts = [];
        foreach ($response as $accountData) {
            $account = new Demo_Db_Crm_Account();
            foreach ($accountData as $key => $value) {
                if (property_exists($account, $key)) {
                    $account->$key = $value;
                }
            }
            $accounts[] = $account;
        }

        // Output the Account objects (for demonstration)
        foreach ($accounts as $account) {
            echo "Account Name: " . $account->Name . "<br>\n";
            echo "Account Number: " . $account->AccountNumber . "<br>\n";
            // ... output other properties as needed
            echo "<br>\n";
        }
    } else {
        echo "No data received or invalid data format.\n";
        if ($response === null && json_last_error() !== JSON_ERROR_NONE) {
            echo "JSON Decode Error: " . json_last_error_msg() . "\n";
        } elseif ($response === null) {
            echo "Response is NULL.\n";
        } else {
            echo "Response is not an array.\n";
            echo "Response Type: " . gettype($response) . "\n"; // Add this line
            echo "Response: " . print_r($response, true) . "\n";
        }
    }
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

Retrieve all records from the BigQuery entity, including the newly added record, using the following command:

Free Trial & More Information

Accessing BigQuery data from PHP can be greatly simplified by leveraging an OData API. CData API Server exposes BigQuery data as an OData service, enabling you to perform CRUD operations (Create, Read, Update, Delete) using standard HTTP requests. This allows you to work with your BigQuery data in a more structured and standardized way.

To see how API Server can simplify your data access, download a free, 30-day trial of CData API Server today! For more general information on our API Server and to see what other data sources we support, refer to our API Server page.