yuji0602’s blog

日々思っていることなど。

BigQueryで複数記載したSQLの中から選択したSQLを実行する

今日、BigQueryのアルファ版に登録している人向けのメールが届いた。メール内容については以下のようになっています。

Dear BigQuery UI Alpha users,

Since the last update a month ago, we've rolled out more features and improvements to the BigQuery Cloud Console UI. If you haven't tried it in a while, or are new to it, please try it out, and let us know what you think!

* Redesigned query results. You will see a more tabular format, with a fixed pagination footer, a default of 50 rows per page, and clear indication of null values (as opposed to strings with "null" as the value, or missing record fields). We're still iterating on this, so it'll look even better soon!
* A few missing features are now implemented:
    * Save to Sheets for query results.
    * Column-based partitioning options in Create Table.
    * Run selected in the Query Editor.
* We've fixed several bugs, including:
    * Various issues with Saved Queries.
    * Better support for hiding and showing the query editor without losing your query job state.
    * Better table lookup behavior in the query editor's autocomplete functionality.
    * Better polling of queries, which should result in slightly more responsive "run query query" behavior, as well as fixed bugs around queries appearing to run forever (please report them if you see them). 
    * Wildcard GCS URLs are now allowed in Create Table.
    * Layout fixes for small screens.

We're still working hard towards our Beta release. You will see a <BETA> badge in the UI, but we haven't officially launched yet! That's there for the public launch; we apologize for any confusion.

If you see things that you've reported and are still missing or not fixed, please know that we will get to them! In the meantime, please continue reporting issues or sending us feature requests to bqui-feedback@google.com.

Thank you for being an BigQuery UI Alpha customer! 

Your BigQuery UI Team

この中で、 Run selected in the Query Editor. が個人的には嬉しかったので実際に試してみました。

BigQueryを開いて、New Queryの中に次のようなクエリを書いてみます。

-- 昨日日付
SELECT
  DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) as yesterday,
  FORMAT_DATE('%Y/%m/%d', DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) as formated_yesterday;

-- 数値にカンマを埋める
SELECT 
  input,
  FORMAT("%'d", input) as formatted
FROM (
  SELECT 123 AS input UNION ALL 
  SELECT 1234 AS input UNION ALL
  SELECT 12345 AS input UNION ALL
  SELECT 123456 AS input UNION ALL
  SELECT 1234567 AS input UNION ALL
  SELECT 12345678 AS input UNION ALL
  SELECT 123456789 AS input
);

このクエリを書いたまま、RUN QUERYボタンを押して実行するとエラーになりました。

f:id:yuji0602:20180517115100p:plain

なので、どちらかのクエリをコメントアウトして実行していました。

今回のメールで来ていた内容はクエリを選択した状態で実行すると良いようです。

f:id:yuji0602:20180517115511p:plain

選択した部分だけ実行できるのでだいぶ便利な気がしますね。

作業が捗りそうです。