blob: f799dfb2e2106be2eed8921bf67941718f175462 [file] [log] [blame]
<!--
Javascript to render AIRFLOW-XXX and PR references in text
as HTML links.
Overrides extrahead block from sphinx_rtd_theme
https://www.sphinx-doc.org/en/master/templating.html
-->
<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js" lang="en" > <!--<![endif]-->
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>airflow.contrib.operators.bigquery_to_mysql_operator &mdash; Airflow Documentation</title>
<link rel="shortcut icon" href="../../../../_static/pin_32.png"/>
<script type="text/javascript" src="../../../../_static/js/modernizr.min.js"></script>
<script type="text/javascript" id="documentation_options" data-url_root="../../../../" src="../../../../_static/documentation_options.js"></script>
<script type="text/javascript" src="../../../../_static/jquery.js"></script>
<script type="text/javascript" src="../../../../_static/underscore.js"></script>
<script type="text/javascript" src="../../../../_static/doctools.js"></script>
<script type="text/javascript" src="../../../../_static/language_data.js"></script>
<script type="text/javascript" src="../../../../_static/jira-links.js"></script>
<script type="text/javascript" src="../../../../_static/js/theme.js"></script>
<link rel="stylesheet" href="../../../../_static/css/theme.css" type="text/css" />
<link rel="stylesheet" href="../../../../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../../../../_static/graphviz.css" type="text/css" />
<link rel="stylesheet" href="../../../../_static/exampleinclude.css" type="text/css" />
<link rel="index" title="Index" href="../../../../genindex.html" />
<link rel="search" title="Search" href="../../../../search.html" />
<script>
</script>
<style>
</style>
</head>
<body class="wy-body-for-nav">
<div class="wy-grid-for-nav">
<nav data-toggle="wy-nav-shift" class="wy-nav-side">
<div class="wy-side-scroll">
<div class="wy-side-nav-search" >
<a href="../../../../index.html" class="icon icon-home"> Airflow
</a>
<div class="version">
1.10.8
</div>
<div role="search">
<form id="rtd-search-form" class="wy-form" action="../../../../search.html" method="get">
<input type="text" name="q" placeholder="Search docs" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
<ul>
<li class="toctree-l1"><a class="reference internal" href="../../../../project.html">Project</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../license.html">License</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../start.html">Quick Start</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../installation.html">Installation</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../tutorial.html">Tutorial</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../howto/index.html">How-to Guides</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../ui.html">UI / Screenshots</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../concepts.html">Concepts</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../profiling.html">Data Profiling</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../cli.html">Command Line Interface Reference</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../scheduler.html">Scheduling &amp; Triggers</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../executor/index.html">Executor</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../plugins.html">Plugins</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../security.html">Security</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../timezone.html">Time zones</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../api.html">REST API Reference</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../integration.html">Integration</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../metrics.html">Metrics</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../errors.html">Error Tracking</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../kubernetes.html">Kubernetes</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../lineage.html">Lineage</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../dag-serialization.html">DAG Serialization</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../changelog.html">Changelog</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../best-practices.html">Best Practices</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../faq.html">FAQ</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../macros.html">Macros reference</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../privacy_notice.html">Privacy Notice</a></li>
</ul>
<p class="caption"><span class="caption-text">References</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="../../../../_api/index.html">Python API</a></li>
<li class="toctree-l1"><a class="reference internal" href="../../../../configurations-ref.html">Configurations</a></li>
</ul>
</div>
</div>
</nav>
<section data-toggle="wy-nav-shift" class="wy-nav-content-wrap">
<nav class="wy-nav-top" aria-label="top navigation">
<i data-toggle="wy-nav-top" class="fa fa-bars"></i>
<a href="../../../../index.html">Airflow</a>
</nav>
<div class="wy-nav-content">
<div class="rst-content">
<div role="navigation" aria-label="breadcrumbs navigation">
<ul class="wy-breadcrumbs">
<li><a href="../../../../index.html">Docs</a> &raquo;</li>
<li><a href="../../../index.html">Module code</a> &raquo;</li>
<li><a href="../operators.html">airflow.contrib.operators</a> &raquo;</li>
<li>airflow.contrib.operators.bigquery_to_mysql_operator</li>
<li class="wy-breadcrumbs-aside">
</li>
</ul>
<hr/>
</div>
<div role="main" class="document" itemscope="itemscope" itemtype="http://schema.org/Article">
<div itemprop="articleBody">
<h1>Source code for airflow.contrib.operators.bigquery_to_mysql_operator</h1><div class="highlight"><pre>
<span></span><span class="c1"># -*- coding: utf-8 -*-</span>
<span class="c1">#</span>
<span class="c1"># Licensed to the Apache Software Foundation (ASF) under one</span>
<span class="c1"># or more contributor license agreements. See the NOTICE file</span>
<span class="c1"># distributed with this work for additional information</span>
<span class="c1"># regarding copyright ownership. The ASF licenses this file</span>
<span class="c1"># to you under the Apache License, Version 2.0 (the</span>
<span class="c1"># &quot;License&quot;); you may not use this file except in compliance</span>
<span class="c1"># with the License. You may obtain a copy of the License at</span>
<span class="c1">#</span>
<span class="c1"># http://www.apache.org/licenses/LICENSE-2.0</span>
<span class="c1">#</span>
<span class="c1"># Unless required by applicable law or agreed to in writing,</span>
<span class="c1"># software distributed under the License is distributed on an</span>
<span class="c1"># &quot;AS IS&quot; BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY</span>
<span class="c1"># KIND, either express or implied. See the License for the</span>
<span class="c1"># specific language governing permissions and limitations</span>
<span class="c1"># under the License.</span>
<span class="sd">&quot;&quot;&quot;</span>
<span class="sd">This module contains a Google BigQuery to MySQL operator.</span>
<span class="sd">&quot;&quot;&quot;</span>
<span class="kn">from</span> <span class="nn">airflow.contrib.hooks.bigquery_hook</span> <span class="kn">import</span> <span class="n">BigQueryHook</span>
<span class="kn">from</span> <span class="nn">airflow.models</span> <span class="kn">import</span> <span class="n">BaseOperator</span>
<span class="kn">from</span> <span class="nn">airflow.utils.decorators</span> <span class="kn">import</span> <span class="n">apply_defaults</span>
<span class="kn">from</span> <span class="nn">airflow.hooks.mysql_hook</span> <span class="kn">import</span> <span class="n">MySqlHook</span>
<div class="viewcode-block" id="BigQueryToMySqlOperator"><a class="viewcode-back" href="../../../../_api/airflow/contrib/operators/bigquery_to_mysql_operator/index.html#airflow.contrib.operators.bigquery_to_mysql_operator.BigQueryToMySqlOperator">[docs]</a><span class="k">class</span> <span class="nc">BigQueryToMySqlOperator</span><span class="p">(</span><span class="n">BaseOperator</span><span class="p">):</span>
<span class="sd">&quot;&quot;&quot;</span>
<span class="sd"> Fetches the data from a BigQuery table (alternatively fetch data for selected columns)</span>
<span class="sd"> and insert that data into a MySQL table.</span>
<span class="sd"> .. note::</span>
<span class="sd"> If you pass fields to ``selected_fields`` which are in different order than the</span>
<span class="sd"> order of columns already in</span>
<span class="sd"> BQ table, the data will still be in the order of BQ table.</span>
<span class="sd"> For example if the BQ table has 3 columns as</span>
<span class="sd"> ``[A,B,C]`` and you pass &#39;B,A&#39; in the ``selected_fields``</span>
<span class="sd"> the data would still be of the form ``&#39;A,B&#39;`` and passed through this form</span>
<span class="sd"> to MySQL</span>
<span class="sd"> **Example**: ::</span>
<span class="sd"> transfer_data = BigQueryToMySqlOperator(</span>
<span class="sd"> task_id=&#39;task_id&#39;,</span>
<span class="sd"> dataset_table=&#39;origin_bq_table&#39;,</span>
<span class="sd"> mysql_table=&#39;dest_table_name&#39;,</span>
<span class="sd"> replace=True,</span>
<span class="sd"> )</span>
<span class="sd"> :param dataset_table: A dotted ``&lt;dataset&gt;.&lt;table&gt;``: the big query table of origin</span>
<span class="sd"> :type dataset_table: str</span>
<span class="sd"> :param max_results: The maximum number of records (rows) to be fetched</span>
<span class="sd"> from the table. (templated)</span>
<span class="sd"> :type max_results: str</span>
<span class="sd"> :param selected_fields: List of fields to return (comma-separated). If</span>
<span class="sd"> unspecified, all fields are returned.</span>
<span class="sd"> :type selected_fields: str</span>
<span class="sd"> :param gcp_conn_id: reference to a specific GCP hook.</span>
<span class="sd"> :type gcp_conn_id: str</span>
<span class="sd"> :param delegate_to: The account to impersonate, if any.</span>
<span class="sd"> For this to work, the service account making the request must have domain-wide</span>
<span class="sd"> delegation enabled.</span>
<span class="sd"> :type delegate_to: str</span>
<span class="sd"> :param mysql_conn_id: reference to a specific mysql hook</span>
<span class="sd"> :type mysql_conn_id: str</span>
<span class="sd"> :param database: name of database which overwrite defined one in connection</span>
<span class="sd"> :type database: str</span>
<span class="sd"> :param replace: Whether to replace instead of insert</span>
<span class="sd"> :type replace: bool</span>
<span class="sd"> :param batch_size: The number of rows to take in each batch</span>
<span class="sd"> :type batch_size: int</span>
<span class="sd"> &quot;&quot;&quot;</span>
<div class="viewcode-block" id="BigQueryToMySqlOperator.template_fields"><a class="viewcode-back" href="../../../../_api/airflow/contrib/operators/bigquery_to_mysql_operator/index.html#airflow.contrib.operators.bigquery_to_mysql_operator.BigQueryToMySqlOperator.template_fields">[docs]</a> <span class="n">template_fields</span> <span class="o">=</span> <span class="p">(</span><span class="s1">&#39;dataset_id&#39;</span><span class="p">,</span> <span class="s1">&#39;table_id&#39;</span><span class="p">,</span> <span class="s1">&#39;mysql_table&#39;</span><span class="p">)</span></div>
<span class="nd">@apply_defaults</span>
<span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span>
<span class="n">dataset_table</span><span class="p">,</span>
<span class="n">mysql_table</span><span class="p">,</span>
<span class="n">selected_fields</span><span class="o">=</span><span class="kc">None</span><span class="p">,</span>
<span class="n">gcp_conn_id</span><span class="o">=</span><span class="s1">&#39;google_cloud_default&#39;</span><span class="p">,</span>
<span class="n">mysql_conn_id</span><span class="o">=</span><span class="s1">&#39;mysql_default&#39;</span><span class="p">,</span>
<span class="n">database</span><span class="o">=</span><span class="kc">None</span><span class="p">,</span>
<span class="n">delegate_to</span><span class="o">=</span><span class="kc">None</span><span class="p">,</span>
<span class="n">replace</span><span class="o">=</span><span class="kc">False</span><span class="p">,</span>
<span class="n">batch_size</span><span class="o">=</span><span class="mi">1000</span><span class="p">,</span>
<span class="o">*</span><span class="n">args</span><span class="p">,</span>
<span class="o">**</span><span class="n">kwargs</span><span class="p">):</span>
<span class="nb">super</span><span class="p">(</span><span class="n">BigQueryToMySqlOperator</span><span class="p">,</span> <span class="bp">self</span><span class="p">)</span><span class="o">.</span><span class="fm">__init__</span><span class="p">(</span><span class="o">*</span><span class="n">args</span><span class="p">,</span> <span class="o">**</span><span class="n">kwargs</span><span class="p">)</span>
<span class="bp">self</span><span class="o">.</span><span class="n">selected_fields</span> <span class="o">=</span> <span class="n">selected_fields</span>
<span class="bp">self</span><span class="o">.</span><span class="n">gcp_conn_id</span> <span class="o">=</span> <span class="n">gcp_conn_id</span>
<span class="bp">self</span><span class="o">.</span><span class="n">mysql_conn_id</span> <span class="o">=</span> <span class="n">mysql_conn_id</span>
<span class="bp">self</span><span class="o">.</span><span class="n">database</span> <span class="o">=</span> <span class="n">database</span>
<span class="bp">self</span><span class="o">.</span><span class="n">mysql_table</span> <span class="o">=</span> <span class="n">mysql_table</span>
<span class="bp">self</span><span class="o">.</span><span class="n">replace</span> <span class="o">=</span> <span class="n">replace</span>
<span class="bp">self</span><span class="o">.</span><span class="n">delegate_to</span> <span class="o">=</span> <span class="n">delegate_to</span>
<span class="bp">self</span><span class="o">.</span><span class="n">batch_size</span> <span class="o">=</span> <span class="n">batch_size</span>
<span class="k">try</span><span class="p">:</span>
<span class="bp">self</span><span class="o">.</span><span class="n">dataset_id</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">table_id</span> <span class="o">=</span> <span class="n">dataset_table</span><span class="o">.</span><span class="n">split</span><span class="p">(</span><span class="s1">&#39;.&#39;</span><span class="p">)</span>
<span class="k">except</span> <span class="ne">ValueError</span><span class="p">:</span>
<span class="k">raise</span> <span class="ne">ValueError</span><span class="p">(</span><span class="s1">&#39;Could not parse </span><span class="si">{}</span><span class="s1"> as &lt;dataset&gt;.&lt;table&gt;&#39;</span>
<span class="o">.</span><span class="n">format</span><span class="p">(</span><span class="n">dataset_table</span><span class="p">))</span>
<div class="viewcode-block" id="BigQueryToMySqlOperator._bq_get_data"><a class="viewcode-back" href="../../../../_api/airflow/contrib/operators/bigquery_to_mysql_operator/index.html#airflow.contrib.operators.bigquery_to_mysql_operator.BigQueryToMySqlOperator._bq_get_data">[docs]</a> <span class="k">def</span> <span class="nf">_bq_get_data</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">log</span><span class="o">.</span><span class="n">info</span><span class="p">(</span><span class="s1">&#39;Fetching Data from:&#39;</span><span class="p">)</span>
<span class="bp">self</span><span class="o">.</span><span class="n">log</span><span class="o">.</span><span class="n">info</span><span class="p">(</span><span class="s1">&#39;Dataset: </span><span class="si">%s</span><span class="s1"> ; Table: </span><span class="si">%s</span><span class="s1">&#39;</span><span class="p">,</span>
<span class="bp">self</span><span class="o">.</span><span class="n">dataset_id</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">table_id</span><span class="p">)</span>
<span class="n">hook</span> <span class="o">=</span> <span class="n">BigQueryHook</span><span class="p">(</span><span class="n">bigquery_conn_id</span><span class="o">=</span><span class="bp">self</span><span class="o">.</span><span class="n">gcp_conn_id</span><span class="p">,</span>
<span class="n">delegate_to</span><span class="o">=</span><span class="bp">self</span><span class="o">.</span><span class="n">delegate_to</span><span class="p">)</span>
<span class="n">conn</span> <span class="o">=</span> <span class="n">hook</span><span class="o">.</span><span class="n">get_conn</span><span class="p">()</span>
<span class="n">cursor</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
<span class="n">i</span> <span class="o">=</span> <span class="mi">0</span>
<span class="k">while</span> <span class="kc">True</span><span class="p">:</span>
<span class="n">response</span> <span class="o">=</span> <span class="n">cursor</span><span class="o">.</span><span class="n">get_tabledata</span><span class="p">(</span><span class="n">dataset_id</span><span class="o">=</span><span class="bp">self</span><span class="o">.</span><span class="n">dataset_id</span><span class="p">,</span>
<span class="n">table_id</span><span class="o">=</span><span class="bp">self</span><span class="o">.</span><span class="n">table_id</span><span class="p">,</span>
<span class="n">max_results</span><span class="o">=</span><span class="bp">self</span><span class="o">.</span><span class="n">batch_size</span><span class="p">,</span>
<span class="n">selected_fields</span><span class="o">=</span><span class="bp">self</span><span class="o">.</span><span class="n">selected_fields</span><span class="p">,</span>
<span class="n">start_index</span><span class="o">=</span><span class="n">i</span> <span class="o">*</span> <span class="bp">self</span><span class="o">.</span><span class="n">batch_size</span><span class="p">)</span>
<span class="k">if</span> <span class="s1">&#39;rows&#39;</span> <span class="ow">in</span> <span class="n">response</span><span class="p">:</span>
<span class="n">rows</span> <span class="o">=</span> <span class="n">response</span><span class="p">[</span><span class="s1">&#39;rows&#39;</span><span class="p">]</span>
<span class="k">else</span><span class="p">:</span>
<span class="bp">self</span><span class="o">.</span><span class="n">log</span><span class="o">.</span><span class="n">info</span><span class="p">(</span><span class="s1">&#39;Job Finished&#39;</span><span class="p">)</span>
<span class="k">return</span>
<span class="bp">self</span><span class="o">.</span><span class="n">log</span><span class="o">.</span><span class="n">info</span><span class="p">(</span><span class="s1">&#39;Total Extracted rows: </span><span class="si">%s</span><span class="s1">&#39;</span><span class="p">,</span> <span class="nb">len</span><span class="p">(</span><span class="n">rows</span><span class="p">)</span> <span class="o">+</span> <span class="n">i</span> <span class="o">*</span> <span class="bp">self</span><span class="o">.</span><span class="n">batch_size</span><span class="p">)</span>
<span class="n">table_data</span> <span class="o">=</span> <span class="p">[]</span>
<span class="k">for</span> <span class="n">dict_row</span> <span class="ow">in</span> <span class="n">rows</span><span class="p">:</span>
<span class="n">single_row</span> <span class="o">=</span> <span class="p">[]</span>
<span class="k">for</span> <span class="n">fields</span> <span class="ow">in</span> <span class="n">dict_row</span><span class="p">[</span><span class="s1">&#39;f&#39;</span><span class="p">]:</span>
<span class="n">single_row</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">fields</span><span class="p">[</span><span class="s1">&#39;v&#39;</span><span class="p">])</span>
<span class="n">table_data</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">single_row</span><span class="p">)</span>
<span class="k">yield</span> <span class="n">table_data</span>
<span class="n">i</span> <span class="o">+=</span> <span class="mi">1</span></div>
<div class="viewcode-block" id="BigQueryToMySqlOperator.execute"><a class="viewcode-back" href="../../../../_api/airflow/contrib/operators/bigquery_to_mysql_operator/index.html#airflow.contrib.operators.bigquery_to_mysql_operator.BigQueryToMySqlOperator.execute">[docs]</a> <span class="k">def</span> <span class="nf">execute</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">context</span><span class="p">):</span>
<span class="n">mysql_hook</span> <span class="o">=</span> <span class="n">MySqlHook</span><span class="p">(</span><span class="n">schema</span><span class="o">=</span><span class="bp">self</span><span class="o">.</span><span class="n">database</span><span class="p">,</span> <span class="n">mysql_conn_id</span><span class="o">=</span><span class="bp">self</span><span class="o">.</span><span class="n">mysql_conn_id</span><span class="p">)</span>
<span class="k">for</span> <span class="n">rows</span> <span class="ow">in</span> <span class="bp">self</span><span class="o">.</span><span class="n">_bq_get_data</span><span class="p">():</span>
<span class="n">mysql_hook</span><span class="o">.</span><span class="n">insert_rows</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">mysql_table</span><span class="p">,</span> <span class="n">rows</span><span class="p">,</span> <span class="n">replace</span><span class="o">=</span><span class="bp">self</span><span class="o">.</span><span class="n">replace</span><span class="p">)</span></div></div>
</pre></div>
</div>
</div>
<footer>
<hr/>
<div role="contentinfo">
<p>
</p>
</div>
Built with <a href="http://sphinx-doc.org/">Sphinx</a> using a <a href="https://github.com/rtfd/sphinx_rtd_theme">theme</a> provided by <a href="https://readthedocs.org">Read the Docs</a>.
<div class="footer">This page uses <a href="https://analytics.google.com/">
Google Analytics</a> to collect statistics. You can disable it by blocking
the JavaScript coming from www.google-analytics.com. Check our
<a href="../../../../privacy_notice.html">Privacy Policy</a>
for more details.
</div>
</footer>
</div>
</div>
</section>
</div>
<script type="text/javascript">
jQuery(function () {
SphinxRtdTheme.Navigation.enable(true);
});
</script>
<!-- Theme Analytics -->
<script>
(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,'script','https://www.google-analytics.com/analytics.js','ga');
ga('create', 'UA-140539454-1', 'auto');
ga('send', 'pageview');
</script>
</body>
</html>