三个点优先级最高,高于两个点,一个点的。
作用一:
传入自定义参数(1),""" 这里面是主要内容 """
'''hive -e
"
SET mapreduce.job.queuename = recom;
SELECT
a.user_log_acct AS uid,
a.time_stamp_click AS create_time
FROM
(
SELECT * FROM recom_yohobuy.click_view_info WHERE date_id = %s
) AS a
LEFT OUTER JOIN
(
SELECT product_skn FROM ods_erp_product.product WHERE date_id = %s
) AS b
ON
a.product_skn = b.product_skn
WHERE
a.user_log_acct != 0
ORDER BY
uid,
create_time
">/data/Algorithm_sun/model_data/click_brand_msort_data_%s.txt
''' % (date_id, date_id, date_id)
传入参数(2)
batchidprefix = "abc"
'''
hive -e
"
SELECT
batch_id
FROM
table_name
WHERE
dt = sysdate( - 1)
AND batch_type = '4'
AND batch_id LIKE '%''' + batchidprefix + '''%';
"
'''
正经的LIKE操作符是这样的 LIKE '%lon%':
解析后是这样的
'''
hive -e
"
SELECT
batch_id
FROM
table_name
WHERE
dt = sysdate( - 1)
AND batch_type = '4'
AND batch_id LIKE '%abc%';
"
'''
所以整个语句应该分成3部分。
part1
'''
hive -e
"
SELECT
batch_id
FROM
table_name
WHERE
dt = sysdate( - 1)
AND batch_type = '4'
AND batch_id LIKE '%
'''
part2
# batchidprefix = "abc"
# + batchidprefix +
+ abc +
# python中
str相加,即相连
part3
'''%';
"
'''
"""连起来是一条完整的语句。"""也是string!
作用二:
定义多行字符串
为避免使用转义换行符 \n,通常会用在定义SQL语句的表达式中没有变量的时候,例如:
import pymysql
try:
# 建立连接
db = pymysql.connect(host='localhost', user='root', passwd='root', db='scraping')
# 创建游标对象
cur = db.cursor()
# SQL建表语句
sql_create_table = """CREATE TABLE CUSTOMER (
FULL_NAME CHAR(20) NOT NULL,
AGE INT,
SEX CHAR(1),
BALANCE FLOAT )"""
cur.execute(sql_create_table)
# SQL 插入语句
sql_insert = """INSERT INTO CUSTOMER (FULL_NAME,
AGE, SEX, BALANCE)
VALUES ('Mac Mohan', 20, 'M', 2000)"""
try:
# 执行sql语句
cur.execute(sql_insert)
# 提交到数据库执行
db.commit()
except:
# 如果发生错误则回滚
db.rollback()
finally:
# 关闭数据库连接
cur.close()
以上述建表语句为例,如果不使用三个双引号,则需要用换行符进行定义,如下:
# SQL建表语句
sql_create_table = "CREATE TABLE CUSTOMER (\nFULL_NAME CHAR(20) NOT NULL,\nAGE INT,\nSEX CHAR(1),\nBALANCE FLOAT )"