前言
我今天正好想做两个事,第一个是,我想把我简书内容备份下来,但是官方提供的备份功能只能备份成markdown,然后发现图片没办法备份。所以我需要把我简书里的所有图片下载下来。
第二个是,我要下载一些jar包,但是只有jar包的名字,没有下载链接,我想生成这些链接然后用wget下载下来。
作为一个程序员,我肯定是要写脚本啦,可以是shell脚本,可以是python或者任何语言都行。但是我不想在这上面浪费太多时间,所以我想到了能不能用几条SQL直接搞定? 然后我实践了下,还真的是可以。
备份简书图片
我首先通过简书的功能把文档都备份下来,里面全部都是markdown. 解压后大概是这样:
![1063603-8f996864dfd856d1.png](https://i-blog.csdnimg.cn/blog_migrate/82f5352307d94cc68c561f0fba9c5550.webp?x-image-process=image/format,png)
接着我把这些文件上传到到MLSQL Console里:
![1063603-ffb393372d8d7a43.png](https://i-blog.csdnimg.cn/blog_migrate/04af82e38488cd59781d57d7b6687128.webp?x-image-process=image/format,png)
接着下载:
![1063603-a28c9766bc99fe55.png](https://i-blog.csdnimg.cn/blog_migrate/d225eb1a647d0e29ae8ea728318fc75d.webp?x-image-process=image/format,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是我们刚才创建的函数。最后保存结果如下:
![1063603-972950a69465570f.png](https://i-blog.csdnimg.cn/blog_migrate/d891b4dd5bf94912e2df4638a5446163.webp?x-image-process=image/format,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;
![1063603-295686783a8731e2.png](https://i-blog.csdnimg.cn/blog_migrate/2b86c43e2dff77036e690f5eaf264ff6.webp?x-image-process=image/format,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;