用MLSQL完成简书图片备份

前言

我今天正好想做两个事，第一个是，我想把我简书内容备份下来，但是官方提供的备份功能只能备份成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 ![link](/yoyo)
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字段格式是这样的：

![image.png](https://upload-images.jianshu.io/upload_images/1063603-f9546ced3af8a9cb.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

我现在想拿到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 ![link](/yoyo)
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;