Before you can query Censys data through Google BigQuery, you'll need to add the Censys Dataset to your BigQuery project. This article explains how to add the dataset once you have created a Google Cloud account.
Note: BigQuery access is only available to users of an enterprise data contract and verified non-commercial researchers. If you have any questions about access, please contact firstname.lastname@example.org.
Currently, the BigQuery Console has a new UI in beta. Through our testing, we've discovered that the ability to easily display the censys-io project does not exist. However, as a work-around, once you're logged into your desired project, following this link, Add the censys-io project to big query, will make the censys-io project available within your project. It is also possible to add the censys-io project using the Classic UI for the initial setup. Once completed, you can switch to the new UI if you wish and all of your changes will be reflected as expected.
Displaying Censys Project
Log in to the Google BigQuery web interface. If your account is set up correctly, you should see your primary project and the standard query interface:
Expand the options associated with your primary project and select Display Project:
Enter censys-io as the Project ID:
Verify that the project is shown in your navigation panel:
Standard SQL Only
You need to use Standard SQL to query the Censys datasets. Unfortunately, Google BigQuery may default to Legacy SQL and the option to change your query to Standard SQL is hidden. Confirm your query is using Standard SQL by opening the query editor and selecting the "More" button.
In the drop-down menu, select "Query Settings" to open a window similar to the one below. Confirm that any references to Legacy SQL are unselected.
Another option is to include the following command at the top of your SQL statement in the web interface as shown below:
<SQL GOES HERE>
For more information on constructing SQL statements, check out the BigQuery SQL Reference.
Note: If you choose to utilize the new console UI after adding the censys-io project standard SQL is set by default. Thus you do not need to specify the language or modify any settings.
Execute Test Query
Once the Censys project has been added, you can query the Censys datasets just like you would query any of our own data. Click Compose Query to get started. For example, you can check for the certificates that are valid for censys.io:
SELECT fingerprint_sha256, parsed.names, metadata.updated_at
UNNEST(parsed.names) AS names
WHERE names = 'censys.io'
Note: Censys datasets can only be queried using Standard SQL. We do not support Legacy SQL.
Now that you've added the datasets, to your account, here's some more information on how to write queries.