替换bq SQL中的撇号或引号 [英] Replacing apostrophe or quotes in bq SQL

问题描述

我刚刚使用bq，Big Query命令行实用程序。我有一个更复杂的SQL子句，不幸的是在SQL语句中有撇号和引号字符。由于这两个字符都在SQL语句中，我正在寻找替换它们的方法，否则其中一个或另一个将被解释为查询结束分隔符。

以下是我正在尝试运行的查询，它在Big Query html界面上效果很好，但使用bq命令行实用程序的效果并不好。取代撇号或引号让这个东西运行的建议？是否有任何选项可以将文件内容传递到bq查询命令中，以便复杂查询可以存储在文件中？ （也比较容易读入一个文件，而不是塞进一行）。

bq查询'SELECT regexp_extract（meta，r'\bldid\：\（。*？）\ '）as bldid FROM stuff.201308010000 LIMIT 10'

在玩完这个之后，看起来这是一个简单的修复。如果引号用于BEG和END分隔符，查询就可以使用。

bq querySELECT regexp_extract（meta，r'\bldid\：：\（ 。*？）\'）as bldid FROM stuff.201308010000 LIMIT 10

不知道为什么这不起作用以标记撇号的开始和结束查询。 这是一个bash问题，不仅仅是一个BigQuery的事情：单引号在单引号字符串中是不可ш的。 Bash在BigQuery甚至看到它之前处理这个字符串。

看看如何在单引号字符串内转义单引号？。

将字符括起来在单引号中保留引号中每个字符的字面值。单引号之间可能不会出现单引号，即使前面加了反斜杠。
http://www.gnu.org/软件/ bash / manual / bashref.html＃单引号

I'm new to using bq, Big Query command line utility. I have a more complex SQL clause that unfortunately has both apostrophe and quote characters within the SQL statement. Since both characters are in the SQL statement I'm looking for some replacement for them, otherwise one or the other will be interpretted as an "end of query" delimitter.

Below is the query that I am trying to run, which works great on the Big Query html interface, but not so great using bq command line utility. Suggestions on replacing the apostrophes or quotes to get this thing running? Is there any option to pass file content into the bq query command so the complex query could be stored in a file? (also easier to read in a file vs crammed onto one line).

bq query 'SELECT regexp_extract(meta, r'\"bldid\":\"(.*?)\"') as bldid FROM stuff.201308010000 LIMIT 10'

[EDIT] After playing around with this a bit more, it looks like this was a simple fix. If quotes are used for BEG and END delimiters the query works.

bq query "SELECT regexp_extract(meta, r'\"bldid\":\"(.*?)\"') as bldid FROM stuff.201308010000 LIMIT 10"

Not sure why this didn't work for apostrophes to mark the beginning and end of the query.

解决方案

This is a bash problem, more than a BigQuery thing: Single quotes are "unquotable" inside a single quoted string. Bash processes the string before BigQuery even sees it.

Take a look at How to escape single-quotes within single-quoted strings?.

"Enclosing characters in single quotes preserves the literal value of each character within the quotes. A single quote may not occur between single quotes, even when preceded by a backslash." http://www.gnu.org/software/bash/manual/bashref.html#Single-Quotes

这篇关于替换bq SQL中的撇号或引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！