前言
我今天正好想做两个事，第一个是，我想把我简书内容备份下来，但是官方提供的备份功能只能备份成markdown,然后发现图片没办法备份。所以我需要把我简书里的所有图片下载下来。
第二个是，我要下载一些jar包，但是只有jar包的名字，没有下载链接，我想生成这些链接然后用wget下载下来。
作为一个程序员，我肯定是要写脚本啦，可以是shell脚本，可以是python或者任何语言都行。但是我不想在这上面浪费太多时间，所以我想到了能不能用几条SQL直接搞定？ 然后我实践了下，还真的是可以。
备份简书图片
我首先通过简书的功能把文档都备份下来，里面全部都是markdown. 解压后大概是这样：
image.png
接着我把这些文件上传到到MLSQL Console里：
image.png
接着下载：
image.png
配置好后运行，然后就会下载到MLSQL的主目录里了。
第一步，加载所有markdown文本：
-- with text we can get all lines in all markdown files.
load text.`oww/*.md` as articleLines;
第二步，抽取图片URL
-- extract markdown link 
set imageUrl='''REGEXP_EXTRACT(value, "(?:!\\[(.*?)\\]\\((.*?)\\))",2)''';
set mdImage='''REGEXP_EXTRACT(value, "(?:!\\[(.*?)\\]\\((.*?)\\))",0)''';
-- use variable make the sql simple
select ${imageUrl} as image_url,${mdImage} as mdImage from articleLines where length(${imageUrl})>0 as imageUrls;
第三步，下载所有图片：
-- download all this images
select crawler_request_image(image_url) as imageBin,mdImage from imageUrls as imageBins;
图片下载比较慢，所以为了防止反复下载图片，我们把图片保存起来：
-- save them as parquet file
save overwrite imageBins as parquet.`jianshu.images`;
接着呢，我们希望把这些图片都保存成图片文件。加载保存的中间结果：
-- again we hope we can save the image as image file.
load parquet.`jianshu.images` as newImageBins;
现在我们要抽取出图片名称，newImageBins里的mdImage字段格式是这样的：
我现在想拿到1063603-f9546ced3af8a9cb.png,正常情况我们可以用SQL正则,这里我偷懒，用点scala代码吧：
register ScriptUDF.`` as getFileName where
lang="scala"
and code='''
def apply(rawFileName:String):String={
rawFileName.split("upload_images/").last.split("\\?").head
}
'''
and udfType="udf";
我创建了一个getFileName的UDF函数，接着我就可以用了：
select getFileName(mdImage) as fileName,imageBin from newImageBins as newImageBinsWithNames;
save overwrite newImageBins as image.`/tmp/images` where fileName="fileName" and imageColumn="imageBin";
getFileName是我们刚才创建的函数。最后保存结果如下：
image.png
终于备份好了
获取jar包链接
首先，我有如下的jar包要处理:
set abc='''
hadoop-annotations-2.7.3.jar
hadoop-auth-2.7.3.jar
hadoop-client-2.7.3.jar
hadoop-common-2.7.3.jar
hadoop-hdfs-2.7.3.jar
hadoop-mapreduce-client-app-2.7.3.jar
hadoop-mapreduce-client-common-2.7.3.jar
hadoop-mapreduce-client-core-2.7.3.jar
hadoop-mapreduce-client-jobclient-2.7.3.jar
hadoop-mapreduce-client-shuffle-2.7.3.jar
hadoop-yarn-api-2.7.3.jar
hadoop-yarn-client-2.7.3.jar
hadoop-yarn-common-2.7.3.jar
hadoop-yarn-server-common-2.7.3.jar
hadoop-yarn-server-web-proxy-2.7.3.jar
''';
我用csv的方式来加载这个文本：
load csvStr.`abc` as jack;
同样的，我个人比较喜欢写点scala代码，所以我又写了个UDF:
register ScriptUDF.`` as link where
code='''
def apply(s:String)={
val fileName = s.split("-").dropRight(1).mkString("-")
s"""http://central.maven.org/maven2/org/apache/hadoop/${fileName}/3.2.0/${s.replaceAll("2.7.3","3.2.0")}"""
}
''';
现在，可以生成链接了：
select link(_c0) from jack as output;
image.png
我把文件保存下来然后用wget命令下载，其实我们也可以用前面的image方式进行保存。
附录
备份图片完整脚本：
-- with text we can get all lines in all markdown files.
load text.`oww/*.md` as articleLines;
-- extract markdown link 
set imageUrl='''REGEXP_EXTRACT(value, "(?:!\\[(.*?)\\]\\((.*?)\\))",2)''';
set mdImage='''REGEXP_EXTRACT(value, "(?:!\\[(.*?)\\]\\((.*?)\\))",0)''';
-- use variable make the sql simple
select ${imageUrl} as image_url,${mdImage} as mdImage from articleLines where length(${imageUrl})>0 as imageUrls;
-- download all this images
select crawler_request_image(image_url) as imageBin,mdImage from imageUrls as imageBins;
-- save them as parquet file
save overwrite imageBins as parquet.`jianshu.images`;
-- again we hope we can save the image as image file.
load parquet.`jianshu.images` as newImageBins;
register ScriptUDF.`` as getFileName where
lang="scala"
and code='''
def apply(rawFileName:String):String={
rawFileName.split("upload_images/").last.split("\\?").head
}
'''
and udfType="udf";
select getFileName(mdImage) as fileName,imageBin from newImageBins as newImageBinsWithNames;
save overwrite newImageBinsWithNames as image.`/tmp/images` where fileName="fileName" and imageColumn="imageBin";
下载Jar包完整脚本:
set abc='''
hadoop-annotations-2.7.3.jar
hadoop-auth-2.7.3.jar
hadoop-client-2.7.3.jar
hadoop-common-2.7.3.jar
hadoop-hdfs-2.7.3.jar
hadoop-mapreduce-client-app-2.7.3.jar
hadoop-mapreduce-client-common-2.7.3.jar
hadoop-mapreduce-client-core-2.7.3.jar
hadoop-mapreduce-client-jobclient-2.7.3.jar
hadoop-mapreduce-client-shuffle-2.7.3.jar
hadoop-yarn-api-2.7.3.jar
hadoop-yarn-client-2.7.3.jar
hadoop-yarn-common-2.7.3.jar
hadoop-yarn-server-common-2.7.3.jar
hadoop-yarn-server-web-proxy-2.7.3.jar
''';
load csvStr.`abc` as jack;
register ScriptUDF.`` as link where
code='''
def apply(s:String)={
//http://central.maven.org/maven2/org/apache/hadoop/hadoop-aliyun/3.2.0/hadoop-aliyun-3.2.0.jar
//http://central.maven.org/maven2/org/apache/hadoop/hadoop-client/3.2.0/hadoop-client-3.2.0.jar
val fileName = s.split("-").dropRight(1).mkString("-")
s"""http://central.maven.org/maven2/org/apache/hadoop/${fileName}/3.2.0/${s.replaceAll("2.7.3","3.2.0")}"""
}
''';
select link(_c0) from jack as output;
load modelParams.`RandomForest` as output;
