publicdata/nih-gov
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
nih-gov/www.ncbi.nlm.nih.gov/pathogens/docs/isolates_gcp/index.html

700 lines
No EOL
38 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<!DOCTYPE html>
<html lang="en" >
<head >
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<!-- Mobile properties -->
<meta name="HandheldFriendly" content="True">
<meta name="MobileOptimized" content="320">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<!-- Stylesheets -->
<link href="/pathogens/static/django_uswds/uswds/css/uswds.css" rel="stylesheet" />
<link rel="stylesheet" href="/pathogens/static/nwds/css/nwds.css" />
<link rel="stylesheet" href="/pathogens/static/nwds/css/header.css" />
<link rel="stylesheet" href="/pathogens/static/nwds/css/footer.css" />
<link rel="stylesheet" href="/pathogens/static/nwds/css/form.css" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css"/>
<title>
Isolates Browser data at Google Cloud Platform -
Pathogen Detection - NCBI
</title>
<!-- extra head -->
<!-- Favicons -->
<link rel="shortcut icon" type="image/ico" href="https://www.ncbi.nlm.nih.gov/coreutils/nwds/img/favicons/favicon.ico" />
<link rel="icon" type="image/png" href="https://www.ncbi.nlm.nih.gov/coreutils/nwds/img/favicons/favicon.png" />
<!-- 192x192, as recommended for Android
http://updates.html5rocks.com/2014/11/Support-for-theme-color-in-Chrome-39-for-Android
-->
<link rel="icon" type="image/png" sizes="192x192" href="https://www.ncbi.nlm.nih.gov/coreutils/nwds/img/favicons/favicon-192.png" />
<!-- 57x57 (precomposed) for iPhone 3GS, pre-2011 iPod Touch and older Android devices -->
<link rel="apple-touch-icon-precomposed" href="https://www.ncbi.nlm.nih.gov/coreutils/nwds/img/favicons/favicon-57.png">
<!-- 72x72 (precomposed) for 1st generation iPad, iPad 2 and iPad mini -->
<link rel="apple-touch-icon-precomposed" sizes="72x72" href="https://www.ncbi.nlm.nih.gov/coreutils/nwds/img/favicons/favicon-72.png">
<!-- 114x114 (precomposed) for iPhone 4, 4S, 5 and post-2011 iPod Touch -->
<link rel="apple-touch-icon-precomposed" sizes="114x114" href="https://www.ncbi.nlm.nih.gov/coreutils/nwds/img/favicons/favicon-114.png">
<!-- 144x144 (precomposed) for iPad 3rd and 4th generation -->
<link rel="apple-touch-icon-precomposed" sizes="144x144" href="https://www.ncbi.nlm.nih.gov/coreutils/nwds/img/favicons/favicon-144.png">
<link rel="stylesheet" href="/pathogens/static/main/styles/app.css" type="text/css" media="screen">
<link rel="stylesheet" href="/pathogens/static/main/styles/breadcrumbs.css" type="text/css" media="screen">
<style type="text/css">
a { text-decoration: underline }
</style>
<!-- Logging params: Pinger defaults -->
<meta name="ncbi_app" content="labs_pathogens" />
<meta name="ncbi_pdid" content="static" />
<meta name="ncbi_phid" content="D0BD57DE768D5F55000042C83BBEAAC0.1.m_9" />
<!-- /extra head -->
<link rel="stylesheet" href="/pathogens/static/main/styles/page-search.css" type="text/css">
</head>
<body >
<a class="usa-skipnav" href="#main_content">Skip to main page content</a>
<!-- ========== BEGIN HEADER ========== -->
<section class="usa-banner">
<div class="usa-accordion">
<header class="usa-banner-header">
<div class="usa-grid usa-banner-inner">
<img src="https://www.ncbi.nlm.nih.gov/coreutils/uswds/img/favicons/favicon-57.png" alt="U.S. flag" />
<p>An official website of the United States government</p>
<button
class="usa-accordion-button usa-banner-button"
aria-expanded="false"
aria-controls="gov-banner-top"
>
<span class="usa-banner-button-text">Here's how you know</span>
</button>
</div>
</header>
<div
class="usa-banner-content usa-grid usa-accordion-content"
id="gov-banner-top"
>
<div class="usa-banner-guidance-gov usa-width-one-half">
<img
class="usa-banner-icon usa-media_block-img"
src="https://www.ncbi.nlm.nih.gov/coreutils/uswds/img/icon-dot-gov.svg"
alt="Dot gov"
/>
<div class="usa-media_block-body">
<p>
<strong>The .gov means it’s official.</strong>
<br />
Federal government websites often end in .gov or .mil. Before
sharing sensitive information, make sure you’re on a federal
government site.
</p>
</div>
</div>
<div class="usa-banner-guidance-ssl usa-width-one-half">
<img
class="usa-banner-icon usa-media_block-img"
src="https://www.ncbi.nlm.nih.gov/coreutils/uswds/img/icon-https.svg"
alt="Https"
/>
<div class="usa-media_block-body">
<p>
<strong>The site is secure.</strong>
<br />
The <strong>https://</strong> ensures that you are connecting to the
official website and that any information you provide is encrypted
and transmitted securely.
</p>
</div>
</div>
</div>
</div>
</section>
<div class="usa-overlay"></div>
<header class="ncbi-header" role="banner" data-section="Header">
<div class="usa-grid">
<div class="usa-width-one-whole">
<div class="ncbi-header__logo">
<a href="https://www.ncbi.nlm.nih.gov/" class="logo" aria-label="NCBI Logo" data-ga-action="click_image" data-ga-label="NIH NLM Logo">
<img src="https://www.ncbi.nlm.nih.gov/coreutils/nwds/img/logos/AgencyLogo.svg" alt="NIH NLM Logo" />
</a>
</div>
<div class="ncbi-header__account">
<a id="account_login" href="https://account.ncbi.nlm.nih.gov" class="usa-button header-button" style="display:none" data-ga-action="open_menu" data-ga-label="account_menu">Log in</a>
<button id="account_info" class="header-button" style="display:none"
aria-controls="account_popup">
<span class="fa fa-user" aria-hidden="true"></span>
<span class="username desktop-only" aria-hidden="true" id="uname_short"></span>
<span class="sr-only">Show account info</span>
</button>
</div>
<div class="ncbi-popup-anchor">
<div class="ncbi-popup account-popup" id="account_popup" aria-hidden="true">
<div class="ncbi-popup-head">
<button class="ncbi-close-button" data-ga-action="close_menu" data-ga-label="account_menu"><span class="fa fa-times"></span><span class="usa-sr-only">Close</span></button>
<h4>Account</h4>
</div>
<div class="account-user-info">
Logged in as:<br/>
<b><span class="username" id="uname_long">username</span></b>
</div>
<div class="account-links">
<ul class="usa-unstyled-list">
<li><a id="account_myncbi" href="/myncbi/" class="set-base-url" data-ga-action="click_menu_item" data-ga-label="account_myncbi">Dashboard</a></li>
<li><a id="account_pubs" href="/myncbi/collections/bibliography/" class="set-base-url" data-ga-action="click_menu_item" data-ga-label="account_pubs">Publications</a></li>
<li><a id="account_settings" href="/account/settings/" class="set-base-url" data-ga-action="click_menu_item" data-ga-label="account_settings">Account settings</a></li>
<li><a id="account_logout" href="/account/signout/" class="set-base-url" data-ga-action="click_menu_item" data-ga-label="account_logout">Log out</a></li>
</ul>
</div>
</div>
</div>
</div>
</div>
</header>
<div role="navigation" aria-label="access keys">
<a id="nws_header_accesskey_0" href="https://www.ncbi.nlm.nih.gov/guide/browsers/#ncbi_accesskeys" class="usa-sr-only" accesskey="0" tabindex="-1">Access keys</a>
<a id="nws_header_accesskey_1" href="https://www.ncbi.nlm.nih.gov" class="usa-sr-only" accesskey="1" tabindex="-1">NCBI Homepage</a>
<a id="nws_header_accesskey_2" href="/myncbi/" class="set-base-url usa-sr-only" accesskey="2" tabindex="-1">MyNCBI Homepage</a>
<a id="nws_header_accesskey_3" href="#maincontent" class="usa-sr-only" accesskey="3" tabindex="-1">Main Content</a>
<a id="nws_header_accesskey_4" href="#" class="usa-sr-only" accesskey="4" tabindex="-1">Main Navigation</a>
</div>
<section data-section="Alerts">
<div class="ncbi-alerts-placeholder"></div>
</section>
<!-- ========== END HEADER ========== -->
<main id="main_content" accesskey="3">
<section class="pathogens-content-page usa-grid">
<div class="usa-width-one-whole ptg-top">
<div><ul id="breadcrumbs" class="usa-unstyled-list breadcrumbs">
<li><a href="https://www.ncbi.nlm.nih.gov/home/health.shtml">Health</a></li>
<li><a href=/pathogens/>Pathogen Detection</a></li>
<li><a href="/pathogens/pathogens_help/">Help</a></li>
<li><a href="/pathogens/docs/gcp/">GCP</a></li>
<li>Isolates Browser data at Google Cloud Platform </li>
</ul>
</div>
<div class="ptg-search">
<input placeholder="Search page" type="search"/><button title="Previous" disabled="disabled"></button><button title="Next" disabled="disabled"></button>
</div>
</div>
<div>
<p><a name="TOC"> </a></p>
<h1 id="isolates-browser-data-at-google-">Isolates Browser data at Google Cloud Platform <a href="/pathogens/pathogens_help/#isolates-browser"><img alt="Isolates Browser Documentation TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h1>
<p><strong>ALPHA RELEASE</strong> -- <em>This is under active development and while we strive to
maintain correctness, it is possible results may be unstable, unavailable, or
incorrect at times. Please contact us by email at pd-help@ncbi.nlm.nih.gov
before relying on this data for production analyses.</em></p>
<div class="toc">
<ul>
<li><a href="#what-data-is-available-on-the-go">What data is available on the Google Cloud?</a><ul>
<li><a href="#pathogen-detection-resources-ava">Pathogen Detection Resources available on the Google Cloud</a></li>
<li><a href="#update-frequency">Update Frequency</a></li>
</ul>
</li>
<li><a href="#getting-started-with-bigquery">Getting started with BigQuery</a></li>
<li><a href="#isolates">ncbi-pathogen-detect.pdbrowser.isolates</a></li>
<li><a href="#isolate_exceptions">ncbi-pathogen-detect.pdbrowser.isolate_exceptions</a></li>
<li><a href="#complex-fields">Complex fields</a></li>
<li><a href="#linking-to-microbigge-data">Linking to MicroBIGG-E data</a></li>
<li><a href="#example-searches">Example searches</a><ul>
<li><a href="#find-the-amr-genes-from-an-isola">Find the AMR genes from an isolate of interest</a></li>
<li><a href="#find-an-isolate-in-isolate_excep">Find an isolate in isolate_exceptions</a></li>
<li><a href="#find-all-salmonella-newport-isol">Find all Salmonella Newport isolates</a></li>
<li><a href="#find-the-most-common-snp-cluster">Find the most common SNP clusters for Salmonella Newport isolates</a></li>
<li><a href="#find-isolates-that-are-carbapene">Find isolates that are carbapenem resistant but don't have a KPC or NDM beta-lactamase</a></li>
<li><a href="#find-all-clusters-where-75-have-">Find all clusters where &gt;75% have a blaKPC</a></li>
</ul>
</li>
<li><a href="#example-searches-that-link-betwe">Example searches that link between MicroBIGG-E and Isolates Browser data in BigQuery</a><ul>
<li><a href="#join-with-the-microbigge-table-t">Join with the MicroBIGG-E table to find isolates that are carbapenem resistant but don't have a known carbapenem resistance gene or allele</a></li>
</ul>
</li>
</ul>
</div>
<h2 id="what-data-is-available-on-the-go">What data is available on the Google Cloud? <a href="#TOC"><img alt="Isolates Browser at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h2>
<p>For a list of all resources see <a href="/pathogens/docs/gcp/">Pathogen Detection Resources at Google Cloud Platform</a></p>
<p>Data from the <a href="/pathogens/isolates">Pathogen Detection Isolates Browser</a> is now available at Google Cloud Platform (GCP) in the <a href="#isolates"><code>ncbi-pathogen-detect.pdbrowser.isolates</code></a> and <a href="#exceptions"><code>ncbi-pathogen-detect.pdbrowser.isolate_exceptions</code></a> tables at Google BigQuery. This data includes all fields available in the web browser and can be searched using <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/introduction">Google Standard
SQL</a>
instead of the <a href="/pathogens/pathogens_help/#solr">SOLR Query Language</a>. This
also permits programmatic access and more complex queries.
BigQuery will also allow you to download tables exceeding the 100,000 row limit
for the <a href="/pathogens/pathogens_help/#isolates-browser-download">Isolates Browser web
download</a>. NCBI is
piloting this in BigQuery to help users leverage the benefits of elastic
scaling and parallel execution of queries. BigQuery has a large collection of
client libraries that can be used within your workflow. You can also interact
with it on a web browser as described below. </p>
<h3 id="pathogen-detection-resources-ava">Pathogen Detection Resources available on the Google Cloud</h3>
<ul>
<li><a href="/pathogens/docs/gcp/">Pathogen Detection Resources at Google Cloud Platform</a></li>
<li><a href="/pathogens/docs/microbigge_gcp/#getting-started-with-bigquery">Getting started with BigQuery</a></li>
<li><a href="/pathogens/docs/microbigge_gcp/">MicroBIGG-E table in BigQuery</a></li>
<li><a href="/pathogens/docs/microbigge_gcp/#contig-sequences">MicroBIGG-E contig sequences in Google Storage buckets</a></li>
<li><a href="/pathogens/docs/microbigge_gcp/#protein-sequences">MicroBIGG-E protein sequences in Google Storage buckets</a></li>
<li><a href="/pathogens/docs/ast_gcp/">AST Browser in BigQuery</a></li>
<li><a href="/pathogens/docs/isolates_gcp/">Isolates Browser table in BigQuery</a></li>
<li><a href="/pathogens/docs/isolates_gcp/#isolate_exceptions">Isolate Exceptions table in BigQuery</a></li>
<li><a href="/pathogens/docs/bioproject_hierarchy_gcp/">BioProject Hierarchy in BigQuery</a>
<!-- asdf_ -->
</li>
</ul>
<h3 id="update-frequency">Update Frequency <a href="#TOC"><img alt="Isolates Browser at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h3>
<p>The isolates and isolate_exceptions tables at Google Cloud BigQuery are updated daily. For this reason the contents may
not agree exactly with those shown in the <a href="/pathogens/isolates/">Isolates
Browser</a>. If you see unexpected discrepancies please let us know by emailing us at pd-help@ncbi.nlm.nih.gov.</p>
<h2 id="getting-started-with-bigquery"><a href="/pathogens/docs/getting_started_bigquery">Getting started with BigQuery</a> <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h2>
<p>Our <a href="/pathogens/docs/getting_started_bigquery">Getting started with BigQuery</a> page has instructions on how to run queries with BigQuery.</p>
<h2 id="isolates"><code>ncbi-pathogen-detect.pdbrowser.isolates</code> <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h2>
<p>This data corresponds to the main table of the <a href="/pathogens/isolates">Pathogen Detection Isolates
Browser</a>. Detailed information on the contents of the
fields in this table is included in the <a href="/pathogens/pathogens_help/#isolates-browser-data-fields">documentation for the Isolates Browser
web interface</a>. Some
fields (<code>AMR_genotypes</code>, <code>virulence_genotypes</code>, <code>stress_genotypes</code>, and
<code>AST_phenotypes</code> are complex and contain <code>AST_phenotypes</code> and contain arrays of
structs. The computed_types field contains a single struct. See the examples
below for ideas of how to search those fields using <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/introduction">Google Standard
SQL</a>. </p>
<h2 id="isolate_exceptions"><code>ncbi-pathogen-detect.pdbrowser.isolate_exceptions</code> <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h2>
<p>The <code>isolate_exceptions</code> table contains information on isolates that failed to process in our system becuase of quality control (QC) failures. See the <a href="/pathogens/pathogens_help/#isolates-browser-exceptions-table">Exceptions table documentation</a> for the Isolates Browser web interface for more details.</p>
<h2 id="complex-fields">Complex fields <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h2>
<p>The <code>AMR_genotypes</code>, <code>stress_genotypes</code>, and <code>virulence_genotypes</code> fields are
<a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays">arrays
of structs</a>, which require syntax that you might not be familiar with coming
from other SQL-based databases. The <code>AST_phenoypes</code> field is a struct which has
fields that are accessed by appending the subfield value to the field value
like <code>AST_phenotypes.serotype</code> See the examples below for how to search based
on those fields.</p>
<h2 id="linking-to-microbigge-data">Linking to MicroBIGG-E data <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h2>
<p>NCBI Pathogen Detection also has <a href="/pathogens/pathogens_help/#microbigge">MicroBIGG-E</a> data in <a href="/pathogens/docs/microbigge_gcp/">Google Cloud BigQuery</a> and links can be made between the <a href="/pathogens/docs/microbigge_gcp/"><code>ncbi-pathogen-detect.pdbrowser.microbigge</code></a> table and the <code>ncbi-pathogen-detect.pdbrowser.isolates</code> tables. Several fields are in common, but generally we recommend joining the two tables using the <code>target_acc</code> field. See below for examples.</p>
<h2 id="example-searches">Example searches <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h2>
<h3 id="find-the-amr-genes-from-an-isola">Find the AMR genes from an isolate of interest <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h3>
<pre><code>SELECT biosample_acc, asm_acc, target_acc, mindiff, AMR_genotypes,
stress_genotypes, virulence_genotypes, computed_types
FROM `ncbi-pathogen-detect.pdbrowser.isolates`
WHERE biosample_acc = 'SAMN08848639'
</code></pre>
<h3 id="find-an-isolate-in-isolate_excep">Find an isolate in isolate_exceptions <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h3>
<pre><code>SELECT *
FROM `ncbi-pathogen-detect.pdbrowser.isolate_exceptions`
WHERE biosample_acc = 'SAMN16279178'
</code></pre>
<h3 id="find-all-salmonella-newport-isol">Find all Salmonella Newport isolates <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h3>
<pre><code>SELECT target_acc, biosample_acc, erd_group, computed_types
FROM `ncbi-pathogen-detect.pdbrowser.isolates`
WHERE computed_types.serotype = 'Newport'
AND taxgroup_name LIKE 'Salmonella%'
</code></pre>
<h3 id="find-the-most-common-snp-cluster">Find the most common SNP clusters for Salmonella Newport isolates <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h3>
<pre><code>SELECT erd_group, count(*) num_newport_isolates
FROM `ncbi-pathogen-detect.pdbrowser.isolates`
WHERE computed_types.serotype = 'Newport'
AND taxgroup_name LIKE 'Salmonella%'
GROUP BY erd_group
ORDER BY num_newport_isolates DESC
LIMIT 5
</code></pre>
<h3 id="find-isolates-that-are-carbapene">Find isolates that are carbapenem resistant but don't have a KPC or NDM beta-lactamase <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h3>
<pre><code>SELECT target_acc
FROM `ncbi-pathogen-detect.pdbrowser.isolates` isolates
WHERE
(SELECT COUNT(1)
FROM UNNEST(isolates.AST_phenotypes)
WHERE antibiotic LIKE '%penem' AND phenotype = 'resistant'
) &gt;= 1
AND
(SELECT COUNT(1)
FROM UNNEST(isolates.AMR_genotypes)
WHERE element LIKE 'blaKPC%' OR element LIKE 'blaNDM%'
) = 0
</code></pre>
<h3 id="find-all-clusters-where-75-have-">Find all clusters where &gt;75% have a blaKPC <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h3>
<pre><code>SELECT kpc_count.erd_group, round(kpc_count.num / erd_size.num * 100) as pct_with_kpc, erd_size.num as snp_cluster_size
FROM
(SELECT i1.erd_group, count(*) num
FROM `ncbi-pathogen-detect.pdbrowser.isolates` i1
WHERE (
(SELECT COUNT(1)
FROM UNNEST(AMR_genotypes) AS AMR_genotypes
WHERE element LIKE 'blaKPC%'
) &gt;= 1
)
GROUP BY erd_group
) kpc_count
LEFT JOIN
(SELECT erd_group, count(*) num
FROM `ncbi-pathogen-detect.pdbrowser.isolates`
GROUP BY erd_group
) erd_size
ON kpc_count.erd_group = erd_size.erd_group
WHERE
kpc_count.num / erd_size.num &gt; 0.75
ORDER BY
pct_with_kpc DESC, snp_cluster_size DESC
</code></pre>
<h2 id="example-searches-that-link-betwe">Example searches that link between MicroBIGG-E and Isolates Browser data in BigQuery <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h2>
<h3 id="join-with-the-microbigge-table-t">Join with the MicroBIGG-E table to find isolates that are carbapenem resistant but don't have a known carbapenem resistance gene or allele <a href="#TOC"><img alt="MicroBIGG-E at Google Cloud Platform TOC" src="/core/assets/pathogens/images/toc_icon.png"/></a> <a href="/pathogens/pathogens_help/"><img alt="Main documentation page" src="/core/assets/pathogens/images/arrowup_blue.gif"/></a></h3>
<pre><code>SELECT isolates.target_acc,
ARRAY(select AS STRUCT antibiotic, phenotype from UNNEST(AST_phenotypes) WHERE antibiotic LIKE "%penem") AST
FROM `ncbi-pathogen-detect.pdbrowser.isolates` isolates
LEFT JOIN `ncbi-pathogen-detect.pdbrowser.microbigge` microbigge
ON isolates.target_acc = microbigge.target_acc
AND microbigge.subclass = 'CARBAPENEM' -- Only carbapenem genes / point mutations
WHERE
(SELECT count(1) FROM unnest(AST_phenotypes) AS ast
WHERE antibiotic like "%penem" AND phenotype = 'resistant') &gt;= 1
AND isolates.amrfinderplus_version IS NOT NULL -- AMRFinderPlus was run on this target
AND isolates.asm_acc IS NOT NULL -- AMRFinderPlus results should be in MicroBIGG-E because assembly is public
AND microbigge.subclass IS NULL -- There are no rows in MicroBIGG-E with subclass = CARBAPENEM
ORDER BY isolates.target_acc
</code></pre>
</div>
</section>
</main>
<!-- ========== BEGIN FOOTER ========== -->
<footer>
<section class="icon-section">
<div id="icon-section-header" class="icon-section_header">Follow NCBI</div>
<div class="grid-container container">
<div class="icon-section_container">
<a class="footer-icon" id="footer_twitter" href="https://twitter.com/ncbi" aria-label="Twitter">
<svg width="40" height="40" viewBox="0 0 40 37" fill="none" xmlns="http://www.w3.org/2000/svg">
<title>Twitter</title>
<g id="twitterx1008">
<path id="path1008"
d="M6.06736 7L16.8778 20.8991L6.00001 32.2H10.2L18.6 23.1L25.668 32.2H34L22.8 17.5L31.9 7H28.4L20.7 15.4L14.401 7H6.06898H6.06736ZM9.66753 8.73423H12.9327L29.7327 30.4658H26.5697L9.66753 8.73423Z"
fill="#5B616B"/>
</g>
</svg>
</a>
<a class="footer-icon" id="footer_facebook" href="https://www.facebook.com/ncbi.nlm" aria-label="Facebook"><svg
data-name="Layer 1" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 300 300">
<title>Facebook</title>
<path class="cls-11"
d="M210.5,115.12H171.74V97.82c0-8.14,5.39-10,9.19-10h27.14V52l-39.32-.12c-35.66,0-42.42,26.68-42.42,43.77v19.48H99.09v36.32h27.24v109h45.41v-109h35Z">
</path>
</svg></a>
<a class="footer-icon" id="footer_linkedin"
href="https://www.linkedin.com/company/ncbinlm"
aria-label="LinkedIn"><svg data-name="Layer 1" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 300 300">
<title>LinkedIn</title>
<path class="cls-11"
d="M101.64,243.37H57.79v-114h43.85Zm-22-131.54h-.26c-13.25,0-21.82-10.36-21.82-21.76,0-11.65,8.84-21.15,22.33-21.15S101.7,78.72,102,90.38C102,101.77,93.4,111.83,79.63,111.83Zm100.93,52.61A17.54,17.54,0,0,0,163,182v61.39H119.18s.51-105.23,0-114H163v13a54.33,54.33,0,0,1,34.54-12.66c26,0,44.39,18.8,44.39,55.29v58.35H198.1V182A17.54,17.54,0,0,0,180.56,164.44Z">
</path>
</svg></a>
<a class="footer-icon" id="footer_github" href="https://github.com/ncbi" aria-label="GitHub"><svg
data-name="Layer 1" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 300 300">
<defs>
<style>
.cls-11,
.cls-12 {
fill: #737373;
}
.cls-11 {
fill-rule: evenodd;
}
</style>
</defs>
<title>GitHub</title>
<path class="cls-11"
d="M151.36,47.28a105.76,105.76,0,0,0-33.43,206.1c5.28,1,7.22-2.3,7.22-5.09,0-2.52-.09-10.85-.14-19.69-29.42,6.4-35.63-12.48-35.63-12.48-4.81-12.22-11.74-15.47-11.74-15.47-9.59-6.56.73-6.43.73-6.43,10.61.75,16.21,10.9,16.21,10.9,9.43,16.17,24.73,11.49,30.77,8.79,1-6.83,3.69-11.5,6.71-14.14C108.57,197.1,83.88,188,83.88,147.51a40.92,40.92,0,0,1,10.9-28.39c-1.1-2.66-4.72-13.42,1-28,0,0,8.88-2.84,29.09,10.84a100.26,100.26,0,0,1,53,0C198,88.3,206.9,91.14,206.9,91.14c5.76,14.56,2.14,25.32,1,28a40.87,40.87,0,0,1,10.89,28.39c0,40.62-24.74,49.56-48.29,52.18,3.79,3.28,7.17,9.71,7.17,19.58,0,14.15-.12,25.54-.12,29,0,2.82,1.9,6.11,7.26,5.07A105.76,105.76,0,0,0,151.36,47.28Z">
</path>
<path class="cls-12"
d="M85.66,199.12c-.23.52-1.06.68-1.81.32s-1.2-1.06-.95-1.59,1.06-.69,1.82-.33,1.21,1.07.94,1.6Zm-1.3-1">
</path>
<path class="cls-12"
d="M90,203.89c-.51.47-1.49.25-2.16-.49a1.61,1.61,0,0,1-.31-2.19c.52-.47,1.47-.25,2.17.49s.82,1.72.3,2.19Zm-1-1.08">
</path>
<path class="cls-12"
d="M94.12,210c-.65.46-1.71,0-2.37-.91s-.64-2.07,0-2.52,1.7,0,2.36.89.65,2.08,0,2.54Zm0,0"></path>
<path class="cls-12"
d="M99.83,215.87c-.58.64-1.82.47-2.72-.41s-1.18-2.06-.6-2.7,1.83-.46,2.74.41,1.2,2.07.58,2.7Zm0,0">
</path>
<path class="cls-12"
d="M107.71,219.29c-.26.82-1.45,1.2-2.64.85s-2-1.34-1.74-2.17,1.44-1.23,2.65-.85,2,1.32,1.73,2.17Zm0,0">
</path>
<path class="cls-12"
d="M116.36,219.92c0,.87-1,1.59-2.24,1.61s-2.29-.68-2.3-1.54,1-1.59,2.26-1.61,2.28.67,2.28,1.54Zm0,0">
</path>
<path class="cls-12"
d="M124.42,218.55c.15.85-.73,1.72-2,1.95s-2.37-.3-2.52-1.14.73-1.75,2-2,2.37.29,2.53,1.16Zm0,0"></path>
</svg></a>
<a class="footer-icon" id="footer_blog" href="https://ncbiinsights.ncbi.nlm.nih.gov/" aria-label="Blog">
<svg id="Layer_1" data-name="Layer 1" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 40 40"><defs><style>.cls-1{fill:#737373;}</style></defs><path class="cls-1" d="M14,30a4,4,0,1,1-4-4,4,4,0,0,1,4,4Zm11,3A19,19,0,0,0,7.05,15a1,1,0,0,0-1,1v3a1,1,0,0,0,.93,1A14,14,0,0,1,20,33.07,1,1,0,0,0,21,34h3a1,1,0,0,0,1-1Zm9,0A28,28,0,0,0,7,6,1,1,0,0,0,6,7v3a1,1,0,0,0,1,1A23,23,0,0,1,29,33a1,1,0,0,0,1,1h3A1,1,0,0,0,34,33Z"/></svg>
</a>
</div>
</div>
</section>
<section class="container-fluid bg-primary">
<div class="container pt-5">
<div class="row mt-3">
<div class="col-lg-3 col-12">
<p><a class="text-white" href="https://www.nlm.nih.gov/socialmedia/index.html">Connect with NLM</a></p>
<ul class="list-inline social_media">
<li class="list-inline-item"><a href="https://twitter.com/NLM_NIH" aria-label="Twitter"
target="_blank" rel="noopener noreferrer">
<svg width="35" height="35" viewBox="0 0 38 35" fill="none" xmlns="http://www.w3.org/2000/svg">
<title>Twitter</title>
<g id="twitterx1009" clip-path="url(#clip0_65276_3946)">
<path id="Vector" d="M17.5006 34.6565C26.9761 34.6565 34.6575 26.9751 34.6575 17.4996C34.6575 8.02416 26.9761 0.342773 17.5006 0.342773C8.02514 0.342773 0.34375 8.02416 0.34375 17.4996C0.34375 26.9751 8.02514 34.6565 17.5006 34.6565Z" fill="#205493" stroke="white" stroke-width="1.2" stroke-miterlimit="10"></path>
<path id="path1009" d="M8.54811 8.5L16.2698 18.4279L8.50001 26.5H11.5L17.5 20L22.5486 26.5H28.5L20.5 16L27 8.5H24.5L19 14.5L14.5007 8.5H8.54927H8.54811ZM11.1197 9.73873H13.4519L25.4519 25.2613H23.1926L11.1197 9.73873Z" fill="white"></path>
</g>
<defs>
<clipPath id="clip0_65276_3946">
<rect width="38" height="38" fill="white"></rect>
</clipPath>
</defs>
</svg></a>
</li>
<li class="list-inline-item"><a href="https://www.facebook.com/nationallibraryofmedicine"
aria-label="Facebook" rel="noopener noreferrer" target="_blank">
<svg version="1.1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" x="0px"
y="0px" viewBox="0 0 249 249" style="enable-background:new 0 0 249 249;" xml:space="preserve">
<style type="text/css">
.st10 {
fill: #FFFFFF;
}
.st110 {
fill: none;
stroke: #FFFFFF;
stroke-width: 8;
stroke-miterlimit: 10;
}
</style>
<title>SM-Facebook</title>
<g>
<g>
<path class="st10" d="M159,99.1h-24V88.4c0-5,3.3-6.2,5.7-6.2h16.8V60l-24.4-0.1c-22.1,0-26.2,16.5-26.2,27.1v12.1H90v22.5h16.9
v67.5H135v-67.5h21.7L159,99.1z"></path>
</g>
</g>
<circle class="st110" cx="123.6" cy="123.2" r="108.2"></circle>
</svg>
</a></li>
<li class="list-inline-item"><a href="https://www.youtube.com/user/NLMNIH" aria-label="Youtube"
target="_blank" rel="noopener noreferrer"><svg version="1.1" xmlns="http://www.w3.org/2000/svg"
xmlns:xlink="http://www.w3.org/1999/xlink" x="0px" y="0px" viewBox="0 0 249 249"
style="enable-background:new 0 0 249 249;" xml:space="preserve">
<title>SM-Youtube</title>
<style type="text/css">
.st4 {
fill: none;
stroke: #FFFFFF;
stroke-width: 8;
stroke-miterlimit: 10;
}
.st5 {
fill: #FFFFFF;
}
</style>
<circle class="st4" cx="124.2" cy="123.4" r="108.2"></circle>
<g transform="translate(0,-952.36218)">
<path class="st5"
d="M88.4,1037.4c-10.4,0-18.7,8.3-18.7,18.7v40.1c0,10.4,8.3,18.7,18.7,18.7h72.1c10.4,0,18.7-8.3,18.7-18.7
v-40.1c0-10.4-8.3-18.7-18.7-18.7H88.4z M115.2,1058.8l29.4,17.4l-29.4,17.4V1058.8z"></path>
</g>
</svg></a></li>
</ul>
</div>
<div class="col-lg-3 col-12">
<p class="address_footer text-white">National Library of Medicine<br>
<a href="https://www.google.com/maps/place/8600+Rockville+Pike,+Bethesda,+MD+20894/@38.9959508,-77.101021,17z/data=!3m1!4b1!4m5!3m4!1s0x89b7c95e25765ddb:0x19156f88b27635b8!8m2!3d38.9959508!4d-77.0988323"
class="text-white" target="_blank" rel="noopener noreferrer">8600 Rockville Pike<br>
Bethesda, MD 20894</a></p>
</div>
<div class="col-lg-3 col-12 centered-lg">
<p><a href="https://www.nlm.nih.gov/web_policies.html" class="text-white">Web Policies</a><br>
<a href="https://www.nih.gov/institutes-nih/nih-office-director/office-communications-public-liaison/freedom-information-act-office"
class="text-white">FOIA</a><br>
<a href="https://www.hhs.gov/vulnerability-disclosure-policy/index.html" class="text-white" id="vdp">HHS Vulnerability Disclosure</a></p>
</div>
<div class="col-lg-3 col-12 centered-lg">
<p><a class="supportLink text-white" href="https://support.nlm.nih.gov/">Help</a><br>
<a href="https://www.nlm.nih.gov/accessibility.html" class="text-white">Accessibility</a><br>
<a href="https://www.nlm.nih.gov/careers/careers.html" class="text-white">Careers</a></p>
</div>
</div>
<div class="row">
<div class="col-lg-12 centered-lg">
<nav class="bottom-links">
<ul class="mt-3">
<li>
<a class="text-white" href="//www.nlm.nih.gov/">NLM</a>
</li>
<li>
<a class="text-white"
href="https://www.nih.gov/">NIH</a>
</li>
<li>
<a class="text-white" href="https://www.hhs.gov/">HHS</a>
</li>
<li>
<a
class="text-white" href="https://www.usa.gov/">USA.gov</a>
</li>
</ul>
</nav>
</div>
</div>
</div>
</section>
</footer>
<!-- ========== END FOOTER ========== -->
<!-- javascript to inject NWDS meta tags. Note: value of nwds_version is updated by "npm version" command -->
<script type="text/javascript">
var nwds_version = "1.2.3";
var meta_nwds_ver = document.createElement('meta');
meta_nwds_ver.name = 'ncbi_nwds_ver';
meta_nwds_ver.content = nwds_version;
document.getElementsByTagName('head')[0].appendChild(meta_nwds_ver);
var meta_nwds = document.createElement('meta');
meta_nwds.name = 'ncbi_nwds';
meta_nwds.content = 'yes';
document.getElementsByTagName('head')[0].appendChild(meta_nwds);
var alertsUrl = "/core/alerts/alerts.js";
if (typeof ncbiBaseUrl !== 'undefined') {
alertsUrl = ncbiBaseUrl + alertsUrl;
}
</script>
<!-- JavaScript -->
<script src="/pathogens/static/django_uswds/uswds/js/uswds.js"></script>
<script src="https://code.jquery.com/jquery-3.5.0.min.js"
integrity="sha256-xNzN2a4ltkB44Mc/Jz3pT4iU1cmeR0FkXs4pru/JxaQ="
crossorigin="anonymous">
</script>
<script>
var fallbackJquery = "/pathogens/static/base/js/jquery-3.5.0.min.js";
window.jQuery || document.write("<script src=" + fallbackJquery + ">\x3C/script>")
</script>
<script src="/pathogens/static/nwds/js/nwds.js" type="text/javascript"> </script>
<script src="/pathogens/static/nwds/js/header.js" type="text/javascript"> </script>
<script src="/pathogens/static/nwds/js/ncbipopup.js" type="text/javascript"> </script>
<script src="/pathogens/static/nwds/js/ncbiclearbutton.js" type="text/javascript"> </script>
<script src="/pathogens/static/nwds/js/override-uswds.js" type="text/javascript"> </script>
<script src="/pathogens/static/nwds/js/ncbifeedback.js" type="text/javascript"> </script>
<script type="text/javascript" src="https://www.ncbi.nlm.nih.gov/core/pinger/pinger.js"> </script>
<script type="text/javascript" src="/pathogens/static/main/scripts/page-search.js" charset="utf-8"></script>
</body>
</html>
Reference in a new issue View git blame Copy permalink